DATE vs DATEVALUE: build a date or parse one
Both hand you a real date value, but from opposite starting points. DATE builds one from separate year, month, and day NUMBERS. DATEVALUE parses one out of a TEXT string that looks like a date. If your inputs are numbers, use DATE; if they're text, use DATEVALUE.
The differences at a glance
| DATE | DATEVALUE | |
|---|---|---|
| Input | Three numbers: year, month, day | One text string, e.g. "2026-07-21" |
| Returns | A date serial number | A date serial number |
| Handles out-of-range parts | Yes — DATE(2026,13,1) rolls to Jan 2027; day 0 = last of prev month | N/A — it reads what's written |
| Locale sensitivity | None — parts are explicit | Yes — "03/04/2026" depends on the machine's date order |
| Fails when | Never (any integers work) | The text isn't a recognizable date -> #VALUE! |
| Compatibility | Universal | Universal (both verified by execution) |
Which should you use?
- DATE — Assembling dates from parts you already have as numbers: first-of-month (DATE(YEAR(A2),MONTH(A2),1)), shifting years, or turning three columns (Y/M/D) into one date. Its roll-over behavior also powers month-end and next-anniversary tricks.
- DATEVALUE — Fixing dates that arrived as TEXT — CSV imports, pasted logs, ISO strings — so they sort and calculate. Prefer unambiguous ISO (yyyy-mm-dd) input; locale-order strings like 03/04/2026 can be misread as March 4 or April 3.
Compatibility (Excel for the web, Sheets & LibreOffice executed; desktop Excel per docs)
Both execute correctly in every LibreOffice version we test (DATE(2026,7,21) and DATEVALUE("2026-07-21") both return the same serial in LibreOffice; Google Sheets executed 2026-08-29 and Excel for the web executed 2026-09-01 both match, every case; desktop Excel per documentation). DATEVALUE parses time-less date strings; its sibling TIMEVALUE parses times, and combining them reconstructs a full timestamp from text. The reverse direction — a real date to formatted text — is TEXT.
Example formulas
| Build a date from parts | =DATE(2026,7,21) |
| Parse a text date | =DATEVALUE("2026-07-21") |
| Three columns into one date | =DATE(A2,B2,C2) |
Full per-version details on each function page: DATE · DATEVALUE.