Google Sheets Date Format Issues? Here’s How To Fix Them

Dates in Google Sheets are not behaving correctly, sorting is wrong, formulas fail, or dates appear as plain text.
This typically shows up as incorrect order (e.g., 12/01 before 02/01), #VALUE! errors, or formulas not recognizing dates.

This guide fixes it step by step.

Why the Issue Happens

• Dates stored as text instead of date values
• Mixed date formats (DD/MM/YYYY vs MM/DD/YYYY)
• Incorrect locale settings
• Imported data with inconsistent formatting
• Hidden spaces or characters
• Using TEXT function incorrectly
• Dates not recognized in formulas (treated as strings)

Step-by-Step Fixes

Step 1: Check If Date is Text or Real Date

Quick test:

=ISNUMBER(A2)

• TRUE → valid date
• FALSE → text (problem)

If FALSE, convert it.

Step 2: Convert Text to Date Using DATEVALUE

Fix:

=DATEVALUE(A2)

This converts text into a proper date serial number.

Step 3: Fix Mixed Date Formats

Example:

• Some cells → DD/MM/YYYY
• Others → MM/DD/YYYY

Sheets may misinterpret dates.

Fix manually:

=DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2))

Adjust based on your format structure.

Step 4: Set Correct Locale

Locale controls default date format.

Fix:

• File → Settings → Locale
• Choose correct country (e.g., India for DD/MM/YYYY)

Then reapply date formatting.

Step 5: Apply Proper Date Format

Even if date is correct, display may be wrong.

Fix:

• Select column
• Format → Number → Date

Or custom format:

• Format → Custom date and time

Step 6: Remove Extra Spaces

Hidden spaces break date recognition.

Fix:

=TRIM(A2)

Combine with conversion:

=DATEVALUE(TRIM(A2))

Step 7: Fix Imported Data

CSV or external data often comes as text.

Fix:

=ARRAYFORMULA(DATEVALUE(A2:A100))

Then format as date.

Step 8: Handle Invalid Date Errors (#VALUE!)

If DATEVALUE fails:

Check:

• Incorrect format
• Invalid date (e.g., 31/02/2024)

Fix by cleaning or correcting input.

Step 9: Fix Sorting Issues

If dates don’t sort correctly:

Problem:

• Dates stored as text

Fix:

• Convert using DATEVALUE
• Then sort:

=SORT(A2:B100, 1, TRUE)

Step 10: Avoid TEXT Function Misuse

Using TEXT converts dates to strings.

Problem:

=TEXT(A2, "dd/mm/yyyy")

This breaks calculations.

Fix:

• Use formatting instead of TEXT when possible
• Only use TEXT for display, not calculations

Common Mistakes

• Assuming formatted text is a real date
• Mixing date formats in one column
• Ignoring locale settings
• Using TEXT instead of proper date formatting
• Not cleaning spaces before conversion
• Sorting text dates instead of real dates

Pro Tips / Better Alternatives

Use VALUE for Quick Conversion

=VALUE(A2)

Works if date format is recognizable.

Extract Date Components

For full control:

=DATE(year, month, day)

Example:

=DATE(2024,1,15)

Standardize Format Across Dataset

Before analysis:

• Convert all dates using DATEVALUE
• Apply uniform format

Use QUERY with Dates

=QUERY(A1:B100, "SELECT A WHERE A > date '2024-01-01'", 1)

Ensure correct date syntax.

Clean Data Pipeline

Before using dates:

=TRIM(CLEAN(A2))

Then convert.

Bottom Line

If date format isn’t working, fix in this order:

1. Check if date is text (ISNUMBER)
2. Convert using DATEVALUE
3. Fix locale settings
4. Apply proper date format
5. Clean spaces and inconsistencies
6. Avoid using TEXT for calculations

Most issues come from dates stored as text and format inconsistencies.
Fix those, and your dates will work correctly in sorting, formulas, and analysis.