Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
Fix the Text date error
Excel & Google SheetsWhat Text date means
A cell can display '2024-01-15' or 'Jan 15 2024' but store those characters as plain text rather than as a date serial number. Formulas like DATEDIF, NETWORKDAYS, and date arithmetic fail or produce wrong results because the engine cannot perform math on a text string.
Common causes
- Dates were imported from a system that outputs non-standard formats (e.g., 'DD/MM/YYYY' in a locale expecting 'MM/DD/YYYY'), which the application cannot parse as dates.
- A date was typed with a delimiter the application does not recognize as a date separator, such as a dot in some locales (e.g., '15.01.2024').
- The column was formatted as Text before dates were entered, so every entry is stored as a string.
- Dates came from a web scrape, API response, or CSV where all values are quoted strings.
- ISO 8601 dates (2024-01-15) were pasted into Excel, which may not auto-convert them depending on regional settings.
Example fix
Broken
=DATEDIF(A2,TODAY(),"D")Fixed
=DATEDIF(DATEVALUE(A2),TODAY(),"D")If A2 contains a text string like '2024-01-15', DATEDIF fails because it expects a date serial number. Wrapping A2 in DATEVALUE() converts the text to a date serial, allowing DATEDIF to calculate the number of days correctly.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
How to fix it
- 1Use DATEVALUE() to convert a text date string to a serial number: =DATEVALUE(A2). Format the result cell as a Date to display it correctly.
- 2If the date format is non-standard (e.g., 'DD/MM/YYYY' in a MM/DD locale), use a formula to reassemble the parts: =DATE(MID(A2,7,4),MID(A2,4,2),LEFT(A2,2)). Adjust positions to match your format.
- 3In Excel, try Data > Text to Columns with a Date format specified in Step 3 of the wizard (choose MDY, DMY, or YMD). This converts text dates in-place.
- 4In Google Sheets, =DATEVALUE(A2) works for many standard formats. For non-standard strings, use a combination of SPLIT, MID, LEFT, RIGHT, and DATE to reconstruct the date.
- 5After converting, verify by checking that the formula bar shows a date serial number (a 5-digit integer in Excel) or that subtracting two dates gives a sensible number of days.
Stop hunting errors by hand.
Upload your spreadsheet and the Auditor flags every Text date and broken formula at once — or paste this one formula and get the fix explained.
Frequently asked
DATEVALUE gives #VALUE! — what is wrong?
DATEVALUE cannot parse the date format in the cell. The string must be in a format your locale recognizes (e.g., 'January 15, 2024' or '1/15/2024' for US locale). For custom formats, use DATE() combined with MID/LEFT/RIGHT to extract the day, month, and year manually.
How can I tell whether a date cell contains a real date or text?
Right-align is numeric (real date). Left-align is text. You can also check: =ISNUMBER(A2) returns TRUE for a real date serial number and FALSE for text.
Does Google Sheets handle date text conversion differently from Excel?
DATEVALUE works in both, but the set of recognized formats differs. Google Sheets is generally more permissive with ISO 8601 (YYYY-MM-DD). If DATEVALUE fails in Sheets, try wrapping the cell in =DATEVALUE(TEXT(A2,"MM/DD/YYYY")) after reconstructing the date parts.