Working with dates in spreadsheets should be straightforward. Yet, Google Sheets users frequently encounter an exasperating issue: you type in a date formatted as Day/Month/Year (e.g., 05/11/2026), and Google Sheets instantly flips it to Month/Day/Year (11/05/2026), shifts it into an unexpected text format, or turns it into a mysterious five-digit number like 46331.
When Google Sheets automatically alters or misinterprets your date entries, the problem is almost always caused by an incorrect spreadsheet Locale setting, an Automatic number formatting rule, or regional discrepancies between collaborators. Here is how to lock in your desired date format permanently.
Top Reasons Google Sheets Alters Date Formats
- Spreadsheet Locale Configured to United States: Google Sheets defaults to the United States locale (
MM/DD/YYYY) unless explicitly configured otherwise. If you live in the UK, Europe, Australia, or India (where dates useDD/MM/YYYY), entering08/04/2026is interpreted as August 4th instead of April 8th. - Dates Converting to Serial Numbers: Internally, Google Sheets stores dates as serial numbers (counting days elapsed since December 30, 1899). If a cell’s formatting switches from Date to Number or Automatic, it displays raw serial digits instead of a human-readable date.
- Dates Stored as Plain Text: If dates are copied from web pages or imported from CSV files with irregular separators (like periods or extra spaces), Google Sheets treats them as text strings, preventing calculation and sorting.
- Collaborator Account Overrides: When multiple users share a spreadsheet, individual browser languages can sometimes cause confusion during input.
Method 1: Change Spreadsheet Locale to Your Region (The Permanent Fix)
Adjusting the workbook locale ensures Google Sheets parses Day, Month, and Year correctly for all users:
- Open your spreadsheet in Google Sheets.
- Click on File in the top navigation menu.
- Select Settings (or Spreadsheet settings).
- In the settings dialogue, locate the Locale drop-down menu.
- Change the locale from United States to your country (e.g., United Kingdom, Australia, or Canada).
- Ensure the Time zone is also set correctly.
- Click Save settings.
Once updated, Google Sheets will natively interpret DD/MM/YYYY entries across all formulas and cells without flipping the day and month.
Method 2: Apply an Explicit Custom Date Format
Prevent cells from reverting to serial numbers or automatic formats:
- Select the column or range of cells containing your dates.
- Click on the Format menu in the top toolbar.
- Hover over Number.
- Click on Custom date and time.
- In the custom dialogue, select your preferred layout (e.g.,
15/08/1930,15 Aug 1930, orYYYY-MM-DD). - Click Apply.
Even if you enter dates using hyphens or slashes, Google Sheets will automatically enforce your custom date structure uniformly.
Method 3: Fix 5-Digit Serial Numbers (e.g. 45230)
If your dates suddenly appear as numbers:
- Highlight the affected cells.
- Click on Format > Number.
- Select Date. The 5-digit integer will instantly convert back into a readable calendar date.
Method 4: Convert Text Dates Using DATEVALUE
If imported dates align to the left side of cells, Google Sheets is treating them as uncalculated text. Use the =DATEVALUE(A2) function in an adjacent column to convert text dates into genuine serial dates, then apply standard date formatting.