Google Sheets Date Format Not Applying? Fixes That Work

If a date format in Google Sheets is not applying, the cells often contain text rather than real date values; changing the display format cannot turn text into dates. Select the cells, choose Format > Number, and apply a date format—but if the display does not change, check the value type, the spreadsheet’s locale, and whether a formula is returning text.

Why date formatting sometimes appears to do nothing

A number format changes how a value is displayed; it does not rewrite the underlying value. Sheets can display a numeric date such as December 5, 2025, in several ways, but it cannot display ordinary text such as “Dec 5, 2025” as a different date merely because you selected a date format. Text may look exactly like a date while remaining text.

Other causes can look similar. The format may have been applied to the wrong range, a formula may return a text string, or the value may have been interpreted using a different date order than expected. A custom format can also hide part of a value, and conditional formatting may make the cell look different from the normal format. Identifying which situation applies is faster than repeatedly choosing formats.

How Google Sheets stores and displays dates

Google Sheets stores valid dates as numeric values, with time represented as a fractional part of a day. The cell’s number format controls how that value appears. For instance, the same date can be shown as 12/5/2025, 5 Dec 2025, or Friday, December 5, depending on the selected format and locale. The displayed style does not change the underlying date.

This distinction matters when sorting, calculating elapsed time, or using dates in formulas. Real date values can generally be sorted chronologically and used in date calculations. Text that resembles a date may sort alphabetically and may not behave as a date in calculations. A cell’s appearance alone is not proof that it contains a date value.

Check whether the cell contains a date or text

Start with a cell that is not changing. Select it and inspect the formula bar to see the stored content, rather than relying only on the formatted display. Also try a quick type check in an empty cell. With the suspected date in A2, enter =ISNUMBER(A2). If the result is TRUE, the content is numeric and may be a date or another number; if it is FALSE, it is text or another nonnumeric value.

That test is useful but not conclusive by itself: a number such as 42 is also numeric, and a date-looking text value is not. To confirm a suspected date, temporarily apply a number format such as Number. A valid date will display as a serial number; text will continue to look like text. Restore the desired date format afterward. If the cell is part of a large range, test a few representative entries because imports can mix real dates and text in the same column.

Convert text dates into real dates

For a text value that Sheets recognizes under the current locale, the DATEVALUE function can convert it to a date serial. With the date text in A2, enter =DATEVALUE(A2) in an empty cell. The result may initially appear as a number; apply Format > Number > Date to that result. If the formula returns an error, Sheets may not recognize the text’s date order or separators.

For a small number of entries, you can correct the source values manually, then format them as dates. For a larger range, use a helper column with the conversion formula, check the results, and then copy the converted cells and use Edit > Paste special > Values only if you need to replace the original text. Keep the original column until you have confirmed that every converted date is correct. Conversion can interpret ambiguous entries differently than intended.

For example, 03/04/2025 can mean March 4 or April 3. DATEVALUE cannot infer which meaning the author intended; its interpretation depends on the spreadsheet’s locale and the input it recognizes. If the intended order is known, use an unambiguous source format or parse the year, month, and day deliberately rather than trusting an ambiguous string. Do not convert a whole column before checking a few known examples.

Check the spreadsheet locale and date order

A spreadsheet’s locale affects default date, number, and currency conventions and how date-like input is interpreted. In Google Sheets in a browser, open File > Settings and inspect the Locale setting. If the source data uses day/month/year but the sheet expects month/day/year, an entry can be misread or treated as text. Change the locale only when it matches the data and the people who use the spreadsheet.

Changing locale is not a substitute for checking existing values. Numeric dates already stored in the sheet remain numeric, although their display conventions may change. Text entries are still text until converted, and ambiguous text should be checked against a known intended date. After changing the setting, verify a few entries and reapply the date display format if needed. Locale settings apply to the spreadsheet, so consider collaborators and other number formats before changing them.

Apply the format to the right cells

Once the values are real dates, select the intended cells or the entire date column, then choose Format > Number and select a date option. For more control, use Format > Number > Custom date and time. In the custom-format dialog, choose or arrange the date components and apply the change. The available display options can depend on locale.

Check the selected range before applying a format. A selection that misses the affected rows—or includes a header—can make the result seem inconsistent.
If a cell shows a number sign sequence such as ####, widen the column. The value may be formatted correctly but not have enough display space.
If only some cells change, check whether those cells are text, whether the range includes them, and whether a formula or conditional format affects their appearance.
If you want the same format for future entries, format the full input column or a sufficiently large range, while keeping headers and unrelated data out of the selection.

When formulas or imported data are involved

A formula can return a real date value or text that merely looks like one. A direct reference to a date cell usually preserves its numeric value, but functions that assemble or format text can produce strings. For example, the TEXT function is designed to return formatted text, not a numeric date. Applying a date number format to that text will not change its appearance. If a result needs to participate in date arithmetic, make sure the formula returns a date value rather than a display string.

Imports and copy-and-paste operations can also bring in dates as text. This is common when a CSV file uses a different date order, when values contain extra spaces, or when an export has placed an apostrophe before the date. Check the original source format and a few sample cells. If the source is under your control, exporting dates in a consistent, unambiguous format can prevent repeated cleanup.

When pasting from another spreadsheet, the source application may bring formatting along with values, or the pasted content may be interpreted as text. Use Edit > Paste special options when you want to control whether you bring over formatting or values. After pasting, check the cell type rather than assuming that a date-looking display is a usable date.

Rule out custom formats and conditional formatting

A custom date format can be valid but not show the components you expect. Reopen Format > Number > Custom date and time and inspect the selected pattern. If you are unsure whether the custom pattern is the issue, choose a standard Date format as a test. If the standard option works, revise the custom pattern rather than changing the underlying value.

Conditional formatting can change text color, background, or other visual cues based on rules, which may make a date seem incorrectly formatted. Open Format > Conditional formatting and review rules that apply to the affected range. Conditional formatting does not convert text into dates, so diagnose the value first; then adjust or remove a conflicting rule if it is responsible for the appearance.

A reliable troubleshooting sequence

Use this order to avoid changing settings unnecessarily:

Select one affected cell and confirm that the correct range is selected.
Check whether the value is numeric or text, and compare it with a date you know is valid in the same sheet.
Apply a standard date format through Format > Number. If that has no effect, investigate text values or formula output.
Check File > Settings for a locale that matches the source date order.
Convert a small sample first, inspect the resulting calendar dates, and only then handle the rest of the range.
Review custom number formats, column width, and conditional formatting if the values are correct but the display still looks wrong.

Changing the locale, repeatedly selecting date formats, and converting an entire column all at once are poor first steps. Each can affect more data than intended or obscure the original problem. A small test cell and a verified sample make it easier to tell whether the issue is type, interpretation, range selection, or appearance.

Frequently Asked Questions

Why does Google Sheets show a date as a number?
A valid date is stored numerically. If the cell is formatted as a number or plain number, Sheets may show its date serial instead of a calendar-style date. Select the cell and choose Format > Number > Date. If it remains an ordinary number, verify that it is actually intended to be a date.

Why does changing the date format not change anything?
The cell may contain text, the selected range may be wrong, or a formula may be returning text. Test the value type and try a standard date format on one known cell. If the result is text, convert it to a date value before applying a display format.

How do I change mm/dd/yyyy to dd/mm/yyyy in Google Sheets?
For a display change, select the cells and choose Format > Number > Custom date and time, then select the desired component order. Check File > Settings as well: the spreadsheet locale affects date conventions and interpretation. If values are text, changing the display format alone will not convert them.

Can I format a date returned by a formula?
Yes, if the formula returns a numeric date value. Select the formula-result cells and apply a date number format. If the formula returns text—for example, a string created with TEXT—a number format cannot turn that string into a date value.

Why are only some dates in a column formatted?
The column may contain a mixture of numeric dates and text, or the selected range may not include every row. Imported data often has mixed types. Check several unchanged cells, convert text entries as needed, and apply the date format to the full intended range.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.