Fix Date Format in Google Sheets: Dates Explained

To fix a date’s appearance in Google Sheets, select the affected cells and choose Format > Number > Date; for a particular order or style, choose Format > Number > Custom date and time. If the cells contain text rather than real dates, formatting alone will not convert them—you must convert or re-enter the values.

Apply the right date format

Start by selecting the cells, column, or range you want to change. Then open Format > Number and select Date. Sheets applies its standard date display to the selected cells. The underlying date value remains the same; only its appearance changes. This is usually the quickest fix when a valid date shows as a number, or when a date appears in a style you do not want.

For more control, choose Format > Number > Custom date and time. In the dialog, select or arrange date components, such as day, month, and year, and choose separators. Common patterns include:

12/31/2025 for a month/day/year display.
31/12/2025 for a day/month/year display.
31 Dec 2025 for a readable abbreviated month.
2025-12-31 for a year-month-day display often used in files and technical records.

The order shown is a display choice, not proof of how the date was originally interpreted. If the date value was parsed incorrectly when entered, changing its format will not repair the wrong day or month. Check the stored value before applying a display style to a large range.

Understand what Sheets stores as a date

Google Sheets stores recognized dates as numeric values, rather than as words. The number represents a day in a sequence, and the cell’s number format tells Sheets to display that value as a date. This is why a date can suddenly appear as a number after someone chooses a different number format. For example, the value for January 1, 2024, can display as a date or as the serial number 45292, depending on the cell format.

This separation between value and appearance is useful. A genuine date can be sorted chronologically, used in date calculations, and included in functions that expect dates. Changing its display from 1/2/2025 to 2 Jan 2025 does not change the date itself. It only changes what people see in the cell.

Text that looks like a date is different. A cell containing the characters 31/12/2025 may remain text if Sheets does not recognize that pattern under the spreadsheet’s locale. It can look convincing while sorting alphabetically or failing in date calculations. Applying a date format changes the display of numeric date values; it cannot make unrecognized text into a numeric date by itself.

Choose a format that suits the data

Pick a display pattern that makes the date clear to everyone who will use the sheet. Numeric dates are compact, but a pattern such as 03/04/2025 is ambiguous: readers may interpret it as March 4 or April 3. Writing the month as a word, or using year-month-day, can prevent that confusion in shared records and exported data.

Consider the purpose of the sheet, not just the width of the column. A short format can work well in a personal tracker, while an audit log or schedule benefits from a format that makes the year and month unmistakable. If a column contains a mix of dates and date-times, decide whether the time matters. A date-only format can hide a time that is still part of the cell value, which may affect sorting or comparisons.

Custom formats can also include weekday names. Use the custom date and time dialog to add the components you need rather than typing a date-looking string into the cell. A typed string is likely to be text; a number format preserves the date value and lets Sheets continue to calculate with it.

Check whether the cells contain text

If applying Format > Number > Date leaves the cell unchanged, or the value does not sort or calculate as expected, check whether it is text. A common clue is that changing between number formats has no effect, but alignment is not a dependable test because alignment can be changed manually. You can also select one cell and inspect its formula bar, or try using it in a date calculation on a copy of the data.

For a text date that Sheets can interpret, the DATEVALUE function can convert it to a date value. With the text date in A2, enter =DATEVALUE(A2) in another cell. For example, if A2 contains the text 12/31/2025 and the spreadsheet’s locale recognizes that as December 31, 2025, the formula returns the numeric date value 46022. Format the result cell as a date to display it in the style you want.

That conversion depends on whether Sheets can interpret the text. If the source is ambiguous or uses a different locale, a formula may return an error or interpret the day and month differently than intended. Do not overwrite the original column until you have checked several converted results, especially where dates such as 04/05/2025 could mean either April 5 or May 4. Once verified, you can copy the converted results and use Edit > Paste special > Values only if you need fixed values rather than formulas.

Set the spreadsheet locale

The spreadsheet’s locale affects how Sheets interprets dates, numbers, and some default formatting. To review it in a browser, choose File > Settings, then check General > Locale. Choose the locale appropriate for the people and conventions the spreadsheet is meant to serve, and save the settings. The setting applies to the spreadsheet, so changing it can affect how other entries are interpreted and displayed as well.

Locale is especially important when entering or importing numeric dates. In one convention, 6/7/2025 means June 7; in another, it means 6 July. If Sheets has already interpreted an ambiguous entry, changing the cell’s format only changes how that existing date appears. It does not reverse the interpretation. Before changing locale in a busy spreadsheet, make a copy and check important dates afterward.

A clear written month, such as 7 Jun 2025, reduces ambiguity when people enter dates manually. For files exchanged between systems, a consistent year-month-day format can also help, but the import process still matters: confirm that Sheets recognizes the incoming values as dates rather than leaving them as text.

Fix dates imported from CSV or another app

Imported data can arrive as recognized dates, plain text, or a mixture of both. If a CSV column displays unexpected numbers, select it and apply a date format first; that may be all that is needed. If it remains unchanged, inspect whether the source values include spaces, apostrophes, inconsistent separators, or a date order that conflicts with the spreadsheet locale.

When importing a file, review the import options and the resulting cells instead of assuming every date was converted correctly. If an option to convert text to numbers, dates, and formulas is offered, its effect may be useful for a clean file but can also change values you intended to keep as text. Preview or check the imported data before relying on it. For a recurring workflow, use a consistent source format and test the import with representative dates, including dates where the day is greater than 12.

For dates stored as text, first determine the source convention. If the source uses day/month/year but the sheet expects month/day/year, blindly converting the column can produce wrong dates without an obvious warning. Preserve the original column, convert a copy, and compare known examples before replacing anything.

Correct common date-format problems

A date displays as a large number: Select the cell and apply Format > Number > Date. The value may already be a valid date with a number format applied.
The date order looks wrong: Check the locale and confirm the underlying date was interpreted correctly. Choose a custom format only after verifying the value.
Formatting does nothing: The entry may be text. Convert it using a suitable method, such as DATEVALUE for text Sheets can parse, then format the resulting date.
Some rows change but others do not: The column may contain mixed types, such as real dates in some rows and text in others. Check and standardize the full range.
A date appears to be one day off: Verify the source’s day/month order and inspect any date-time or time-zone conversion in the workflow. A display format cannot correct a value that was converted incorrectly.
The cell shows number signs: Widen the column. A date or date-time may not fit in the available space.

For a whole column, select only the relevant range when possible. Formatting an entire column can be convenient for future entries, but it can also affect headers, notes, or other values that are not dates. After fixing the display, sort a small test range or check a known date to make sure the result still behaves like a date.

Keep dates consistent when collaborating

People working in the same spreadsheet may have different regional date conventions, but the spreadsheet has a locale setting that governs its interpretation and default formats. Agree on a clear entry convention, especially for dates typed by hand. A displayed format can help communicate that convention, but it does not prevent someone from pasting ambiguous text into the column.

For dependable shared data, use a header that says what the field means, keep dates in a consistent format, and avoid mixing dates with explanatory text in the same column. If you share a sheet as a file or export it, check the exported result too. CSV files do not preserve cell formatting in the same way as a spreadsheet, so a date that looked clear in Sheets may appear differently in another program.

How Google Sheets differs from Excel

The basic idea is the same in Google Sheets and Excel: a recognized date is stored as a value, and number formatting controls its appearance. The menus differ. In Sheets, use Format > Number; in desktop Excel for Microsoft 365 on Windows, date formats are available from Home > Number Format, with more options in the Format Cells dialog.

Locale and regional settings are also managed differently. Google Sheets has a spreadsheet-level locale under File > Settings. Excel’s behavior depends on the application and operating-system regional settings, and workbooks can be opened by people whose systems use other conventions. When moving data between the programs, verify both the displayed dates and the underlying values instead of assuming that matching-looking text was interpreted identically.

Frequently Asked Questions

Why does Google Sheets show a date as a number?

Sheets stores recognized dates as numeric values. The cell is probably using a number format rather than a date format. Select it and choose Format > Number > Date to display the value as a date.

Why does changing the date format not work?

The cell may contain text rather than a recognized date value. A date format changes the appearance of numeric values; it does not automatically convert arbitrary text. Convert the text using a method Sheets can interpret, then format the resulting value.

How do I change mm/dd/yyyy to dd/mm/yyyy?

Select the cells and choose Format > Number > Custom date and time, then arrange the components in day, month, year order. Confirm the underlying dates first, because formatting does not correct an earlier month/day interpretation.

Where do I change the date locale in Google Sheets?

In the browser, open File > Settings and review General > Locale. The locale affects the spreadsheet’s date and number conventions, so check existing data after changing it.

Can I make Sheets enter dates in ISO-style order?

You can display recognized dates in year-month-day order with Format > Number > Custom date and time. This changes the display, not the value or the rules used to interpret every pasted entry, so keep the spreadsheet locale and incoming data in mind.

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.