Date & Time
DATEVALUE function: convert text dates to serial numbers safely
Turn imported ISO date strings into real spreadsheet serial dates with DATEVALUE, and guard mixed columns where some cells are already numeric dates.
Why text dates fail in date math and SUMIFS
Imported CSV feeds and API exports often land as text strings such as "2026-04-15" instead of numeric spreadsheet dates. Because text sorts alphabetically and is ignored by numeric comparisons, DATEVALUE(date_text) parses the string into its underlying integer serial number (46127 for 2026-04-15).
Once converted to serial integers, subtracting two dates yields exact elapsed calendar days (46142 - 46127 = 15 days).
| Cell | Stored Value | ISTEXT | DATEVALUE Result | Safe Mixed Formula |
|---|---|---|---|---|
| A2 | ="2026-04-15" (text) | TRUE | 46127 | 46127 |
| A3 | ="2026-04-30" (text) | TRUE | 46142 | 46142 |
| A4 | 2026-04-01 (already numeric 46113) | FALSE | #VALUE! | 46113 |
The mixed-column trap: DATEVALUE rejects numeric dates
DATEVALUE strictly requires a text argument. If a column mixes text dates with cells that were already parsed as numeric serial dates (like A4), bare DATEVALUE(A4) returns #VALUE!. Checking ISNUMBER first handles both types cleanly.
=IF(ISNUMBER(A4), A4, DATEVALUE(A4))Prefer unambiguous ISO YYYY-MM-DD strings
Slash strings like "04/05/2026" can mean April 5 or May 4. Excel DATEVALUE parsing depends on system date settings, so even ISO input should be tested in the target environment. These ISO examples work in Arte Sheets; for fixed YYYY-MM-DD input, explicit DATE construction from year, month and day avoids relying on locale-sensitive parsing.
=DATEVALUE(A3) - DATEVALUE(A2)Try the example
Use the original values or change them to test your understanding.