Why Google Sheets Shows the Wrong Date Format

When Google Sheets turns 04/07 into 07/04, or formats a number as a surprising date, the sheet is usually interpreting the value with a different locale or treating text as a serial number. Set the file's locale, convert text dates, then apply the display format.

Last verified: July 28, 2026.

First Check: Is the Value a Date or Text?#

Click the cell and look at the formula bar. A real date is stored as a number even though it is displayed as a date. Text copied from a website may look like a date but cannot be sorted or used reliably in date formulas.

If a numeric value such as 51422 becomes a date in the year 2040, Sheets is correctly reading it as a date serial—not recognizing it as a DDMMYY or MMDDYY code. Re-enter the source value in an unambiguous format instead of applying Date to the number.

Set the Spreadsheet Locale#

1. Open File > Settings.
2. Under General, choose the country that matches your source data.
3. Click Save settings and reload the sheet if prompted.

The locale controls default date and number formats for the entire spreadsheet. It affects everyone working in that file, so agree on one locale before importing shared data.

Apply a Display Format After the Locale Is Correct#

1. Select the date column.
2. Choose Format > Number > Date for the standard locale format.
3. Use Format > Number > Custom date and time when you need an exact pattern such as yyyy-mm-dd.

Formatting changes how a valid date is displayed; it does not repair text that was imported with the wrong order.

Convert Text Dates Safely#

For predictable ISO-style text such as 2026-07-28, use:

=DATEVALUE(A2)

Then format the result as a date. For ambiguous strings such as 04/07/2026, decide whether the source means 4 July or April 7 before converting. If necessary, split the text into day, month, and year and rebuild it with DATE(year, month, day).

Why the Date Still Looks Wrong#

• The file locale is wrong: Changing your browser language does not change the spreadsheet locale.
• The source is text: Date formatting cannot turn arbitrary text into a date.
• The number is a serial: A plain number is counted from Sheets' date system when you apply Date.
• Mixed formats: Clean and convert one column before sorting or calculating.

Quick Checklist#

• Confirm whether the cell contains a date value or text.
• Set File > Settings > Locale to match the source.
• Convert text with DATEVALUE only when the string format is understood.
• Apply Date or Custom date and time after conversion.
• Test sorting and a simple date subtraction before using the column in reports.

Google's spreadsheet location and calculation settings explains how locale controls default date and number formatting.

Related Google Workspace Fixes#

• How to Fix “Array Result Was Not Expanded” in Google Sheets
• Select Multiple Options from a Google Sheets Dropdown

Get the Add-On#

Get the Add-On - Free

Text To Table Converter

Work faster inside Google Forms, Docs, Sheets, and Slides: Install the free Text To Table Converter add-on for quiz building, choice syncing, QR codes, document tools, and more.

Install free