Google Sheets Date Format Not Working? Here’s How to Fix It

Google Sheets Date Format Not Working? Here's How to Fix It

If Google Sheets date formatting is not working, first check whether the cell contains a real date value rather than text; select it and choose Format > Number > Date. If it is text, correct the spreadsheet’s locale or convert the text to a date, then apply the format.

Start by identifying what is wrong

“Date format not working” can describe several different problems: a date stays in an unexpected order, changing the format has no effect, a number appears instead of a date, or a date includes an unwanted time. The right fix depends on whether Sheets recognizes the cell’s contents as a date and on what display you want.

Try selecting the cell and looking at the formula bar. If the entry is a recognizable date value, changing its number format should change how it looks without changing the underlying date. If it is text that merely resembles a date, a number format usually cannot change it. A useful first check is to select the cell and choose Format > Number > Date. If the display remains exactly the same, investigate whether the entry is text or whether the format was applied to a different range.

Also check whether the issue affects one cell or an entire column. One troublesome value often points to a differently formatted or imported entry. A whole column that interprets dates inconsistently may point to the spreadsheet’s locale or to mixed source formats.

How Sheets stores dates and formats them

Google Sheets stores dates as numeric values, not as a special kind of visible text. A date is represented by a day count, and a time can be represented as a fraction of a day. The cell’s number format tells Sheets how to display that value. For example, one date value might display as 3/8/2025, 8 Mar 2025, or Saturday, March 8, depending on the chosen format.

This distinction explains a common misconception: formatting changes appearance, but it does not reliably turn arbitrary text into a date. If a cell contains the text March 8, 2025, applying a date format may leave the text unchanged. Conversely, if a cell contains a recognized date and displays as a number, changing its format to Date can reveal the calendar date.

Formatting also does not change the underlying day. If you display the same date as 08/03/2025 or 3/8/2025, the value can still be identical; only the presentation differs. This matters when sorting, filtering, calculating elapsed days, or comparing dates in formulas, because real date values behave differently from text strings.

Apply a date format in Google Sheets

In Sheets in a web browser, select the cells and choose Format > Number > Date. For a particular display, use Format > Number > Custom date and time, select or enter a pattern, and apply it. The pattern changes how a date value appears; it does not repair text that Sheets has not recognized as a date.

Common patterns include m/d/yyyy for 3/8/2025, dd/mm/yyyy for 08/03/2025, and mmm d, yyyy for Mar 8, 2025. Be careful with the first two: 3/8/2025 is ambiguous without knowing whether the order is month/day or day/month. If the custom format includes hours and minutes, a date value with no time may show midnight, while a date-time value may show the time stored in the cell.

If a format was applied to only part of the data, select the full intended range before changing it. You can select a column by clicking its letter, but doing so may also affect headings or unrelated values in that column. For a spreadsheet with mixed content, selecting only the data cells is usually safer. In Google Sheets, these menu paths differ from Excel’s ribbon; use the Sheets menus rather than Excel instructions when working in a browser.

Check the spreadsheet locale

The spreadsheet locale influences how Sheets interprets dates entered in formats that can be read more than one way. For example, 04/05/2025 can mean April 5 or 4 May. A locale that expects month/day/year may interpret it differently from one that expects day/month/year. Locale can also affect default date display and other regional conventions.

To review it, choose File > Settings and check the Locale setting. If the sheet is intended for a different region, select the appropriate locale and save the settings. Because a locale change can affect date interpretation, number separators, and currency conventions across the spreadsheet, consider the consequences before changing a shared or established file. It is not just a cosmetic setting.

For data exchanged with people in different regions, avoid relying on ambiguous numeric dates. Use a clear written month, such as 5 Apr 2025, or use an unambiguous year-first form such as 2025-04-05 when entering or preparing source data. Even then, verify how the imported values were interpreted rather than assuming the display proves the underlying value is correct.

Convert date-like text into real dates

First decide whether the text uses a consistent, recognizable pattern. Sheets provides DATEVALUE for converting text that it can interpret as a date. With the text March 8, 2025 in A2, =DATEVALUE(A2) returns the date value for March 8, 2025. Apply a date format to the formula cell if the result appears as a number. The exact text DATEVALUE accepts can depend on the spreadsheet’s locale, so it may not be the best choice for ambiguous input.

For consistently structured text in year-month-day order, construct the date from its parts instead of asking Sheets to guess. If A2 contains the text 2025-03-08 in exactly that fixed pattern, =DATE(VALUE(LEFT(A2,4)),VALUE(MID(A2,6,2)),VALUE(RIGHT(A2,2))) returns March 8, 2025. This approach assumes four year digits, two month digits, two day digits, and hyphens in those positions. It is not suitable for values with a different order or inconsistent spacing.

When converting a large range, put the conversion formula in a spare column, fill it down, and inspect a sample of the results before replacing the original data. Once the converted values are correct, copy the results and use Edit > Paste special > Values only if you need to replace formulas with fixed values. Keep the original data until you have checked the conversion, especially when the source includes ambiguous dates.

Diagnose text, numbers, and imported data

A quick test can distinguish a numeric date from text. With the date or date-like entry in A2, =ISNUMBER(A2) returns TRUE for a real Sheets date value and FALSE for text. A TRUE result does not prove that the date is the intended day, so check ambiguous entries against the source. A date displayed as a number may simply need a date format; a FALSE result suggests that conversion or re-entry is needed.

Imported CSV files, copied website tables, and data pasted from other systems can contain text dates, leading apostrophes, extra spaces, or inconsistent date orders. Some rows may be valid date values while others remain text, even when they look alike. Sorting a mixed column can reveal this: text and numeric dates may not sort together in calendar order. Look for outliers and inspect individual cells rather than applying a format repeatedly.

For a small number of entries, re-enter each date in a clear form that matches the sheet’s locale, then apply the desired display format.
For a consistent text pattern, use DATEVALUE or construct the date from its parts, then check several converted results.
For a file you import regularly, standardize the source date format and review the import options and resulting values each time.
If only a few cells are affected, compare their contents and number formats with nearby cells before changing the whole column.

Formatting a whole range is useful only after the values are consistent. Otherwise, it can make valid dates look uniform while leaving text entries unchanged, creating the impression that the entire column is fixed.

Fix unexpected times, clipping, and other display issues

If a date shows an unwanted time, the underlying value may include both a date and a time. Choose a date-only format through Format > Number > Date, or use a custom date pattern without time tokens. This hides the time from view but does not remove it from the stored value. If calculations or comparisons must ignore the time, decide whether to create date-only values rather than relying on appearance alone.

If a value looks like a serial number, try Format > Number > Date. If it then displays as a plausible but incorrect day, the problem may be a bad source value or an earlier interpretation error, not a display setting. If the cell shows hashes or the date is clipped, widen the column by dragging the boundary between column headings. A narrow column can make a correct value hard to read.

For date-time data that seems shifted by hours, check File > Settings for the spreadsheet’s time zone and compare the cell with the system or source that supplied it. A time-zone mismatch can matter when data contains timestamps or when another process creates or imports them. Changing the time-zone setting is not a general repair for dates that were parsed in the wrong month/day order, and it may affect how time-based values are interpreted or displayed.

Keep dates reliable in shared spreadsheets

When several people enter dates, agree on one clear input convention and document it near the data. A written month or a year-first format avoids many day/month misunderstandings. Keep the spreadsheet locale appropriate for the team, and avoid mixing hand-entered dates with date strings copied from systems that use a different convention.

For recurring imports, check both the visible result and the value type after each import. Use a separate cleaned-data column when you need to transform source text, and preserve the original column until the conversion is verified. If formulas, filters, or charts depend on the dates, test a few representative rows after changing locale or replacing values; a display that looks correct is not enough to confirm that every row contains the expected date.

Frequently Asked Questions

Why does changing the date format do nothing? The cell may contain text rather than a date value, or the format may have been applied to the wrong range. Check the formula bar and test the cell with ISNUMBER; convert text if necessary, then apply a date format.

Why does Google Sheets show a number instead of a date? The value may be a date serial displayed with a number format. Select the cell and choose Format > Number > Date. If the resulting date is not plausible, check how the original value was entered or imported.

How do I change dates from month/day/year to day/month/year? To change only the display, select the cells and choose Format > Number > Custom date and time, then use a day-first pattern such as dd/mm/yyyy. If the underlying dates were interpreted incorrectly, fix the values as well; changing the display alone will not correct them.

Why are some dates in a column correct and others not? The column may mix real date values with text, or the source may contain different date orders. Inspect affected cells, compare them with the source, and convert only after identifying the pattern. Applying one format to the column does not normalize mixed data.

Does changing the locale change existing dates? It can affect regional conventions and how ambiguous date text is interpreted, so review the values after changing it. A locale setting is not a substitute for checking imported or previously misread dates, particularly in a shared spreadsheet.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.