Normalize Dates in Google Sheets with DATEVALUE and TEXT
January 27th, 2026
Imported data often contains date strings in inconsistent formats. This post shows how to normalize dates so you can sort, filter, and report without errors using DATEVALUE and TEXT.
Step 1: Convert text to a real date
If a cell contains a date string like 2026-02-06 or 02/06/2026, try:
=DATEVALUE(A2)
DATEVALUE converts a supported date string into a real date value that Sheets understands.
Step 2: Standardize the display
Once the date is real, format it consistently with TEXT:
=TEXT(DATEVALUE(A2), "yyyy-mm-dd")
This keeps the underlying value as a date, but shows it in a predictable format.
Handling timestamps
If you have timestamps like 2026-02-06 09:15:00, split the date portion first:
=DATEVALUE(LEFT(A2, 10))
Then format it:
=TEXT(DATEVALUE(LEFT(A2, 10)), "yyyy-mm-dd")
Make it scalable with ARRAYFORMULA
=ARRAYFORMULA(IF(A2:A="", "", TEXT(DATEVALUE(LEFT(A2:A, 10)), "yyyy-mm-dd")))
This handles blank rows and keeps the output clean.
Troubleshooting tips
- If DATEVALUE returns an error, check the locale settings for the spreadsheet.
- For ambiguous dates like
03/04/2026, make sure the format matches your locale. - If you see time zones shifting, keep your output as a date only (no time).