[Fixed] Date Format Not Working in Google Sheets

Google Sheets offers a variety of date formatting options, but sometimes your dates don’t behave as expected. They may not change format, appear as plain text, or return errors in formulas. This issue usually stems from inconsistent data entry or cells not being recognized as actual dates.

In this article, we will guide you through the most common reasons why date formatting fails and provide step-by-step instructions on how to fix each issue.

This method helps you determine if the value you’re trying to format is being recognized as a date by Google Sheets. Sometimes, data appears to be a date but is actually stored as plain text, which prevents formatting from being applied correctly.

This is the dataset we will use to demonstrate the methods.

Steps:

➤ Select the cell or range where the date formatting is not working.
➤ Go to the top menu and click: Format >> Number >> Number.

➤ Observe what happens:

• If the value changes to a number (e.g., 45123), it is a valid date (Google Sheets stores dates as serial numbers).

• If nothing changes and it still looks like “2024-05-01” or “01/05/2024”, it’s just plain text, not a date.

➤ You can also test by using a formula:

Check how we have used it in our dataset to identify formatted dates.

• If it returns TRUE, the cell is a valid date.
• If it returns FALSE, the cell contains text that just looks like a date.

➤ If the value is not a date, you’ll need to convert it.

Even though the cell might look like a date, Sheets won’t apply formatting unless it’s internally stored as one.

If your date is stored as text, this method helps convert it into a real date format that Google Sheets can recognize and format correctly. We will be using the same dataset as last time, and only work on the dates that we had identified as text.

Column C in our table already uses the ISDATE function from the first method to test if each entry is recognized as a real date. When it returns TRUE, our conversion has worked.

Dates Wrapped With Quotes Treated as Text in Google Sheet

Steps:

➤ Suppose cell A3 contains a text date like “05/01/2024”.

➤ Check if the date is wrapped with quotation marks, remove the quotes if yes.

You can notice that the date will automatically move to the right, hinting that it has been converted to the date format.

Dates with Dots Being Treated as Text in Google Sheets

Steps:

➤ As you can see from the dataset we are using, not all dates are wrapped with quotation marks. Some are formatted as a date because they are written with dots.

➤ Simply remove the dots from the date and replace them with slashes. It will automatically format as a date.

Dates with Invalid Month or Day Giving Error in Google Sheets

Steps:

➤ In the same dataset, check cell  A8. The date has an invalid month (13) in it. This triggers a logical error in Google Sheets.

➤ If you have incorrectly written ’13’ as your month, then correct it to the correct month. Your formatting should work.

If you believe you have entered the date correctly and the ‘13’ in your date represents the day of the month January, then you need to change the way you have written the date.

➤ Since Google Sheets is treating your date as a ‘yyyy-mm-dd’ format, you can try to change it to a ‘yyyy-dd-mm’ format.

As you can see, cell A8 is now being treated as a date.

This method helps fix the issue by removing invisible characters from the text. Once cleaned, the value can be safely converted into a date using the DATEVALUE function.

Again, Column C in our table already uses the ISDATE function from the first method to test if each entry is recognized as a real date. When it returns TRUE, our conversions have been successful.

We will also be using this new dataset to demonstrate this method.

Steps:

➤ In Column D, cell D2 use this formula and pull the fill handle down.

➤ After applying the formula, select the entire column where you placed the formula (e.g., Column D).
➤ Go to the top menu and click: Format >> Number >> Date

➤ This step converts the serial numbers into readable dates like 2024-05-01.

➤ We will apply the ISDATE function in column E to see if our new dates have been formatted correctly or not.

As you can see ISDATE returns TRUE, which means the dates are now formatted correctly.

Frequently Asked Questions

Why won’t Google Sheets format my dates correctly?

Google Sheets may misinterpret dates due to locale settings. If your input format doesn’t match the locale, it treats the data as plain text instead of real dates.

How do I fix unrecognized date formats in Google Sheets?

Change the spreadsheet’s locale under File >> Settings to match your date format. Then reformat the cells using Format >> Number >> Date or custom format.

What does changing the locale affect in Google Sheets?

Changing the locale adjusts the default formats for dates, times, numbers, and currency, ensuring that input values are interpreted and displayed according to local conventions.

My dates still look wrong after formatting. What now?

Ensure your original date entries are real dates, not text. Use the ISDATE function to check. If false, remove hidden characters or re-enter the data manually.

Wrapping Up

Date formatting issues in Google Sheets arise from incorrect locale settings or hidden text. Ensure your locale matches the date format, remove hidden characters, and use the ISDATE function to convert text into dates. Begin with a test dataset and troubleshoot step-by-step, particularly when copying from external sources like CSV files or emails. Once resolved, date calculations, sorting, and formatting will work smoothly. If issues persist, use helper columns to isolate problems and clean data with formulas like DATEVALUE, SPLIT, or SUBSTITUTE.