DATEVALUE converts a recognized text date into the serial number Google Sheets uses for dates. Format that serial as a date, or use it directly in sorting, filtering, and calculations.
For text in A2, use =DATEVALUE(A2). Date interpretation depends on the spreadsheet locale, so prefer clear source formats.
DATEVALUE Function Syntax in Google Sheets
=DATEVALUE(date_string)date_string is text that Sheets recognizes as a date. A typed string inside the formula needs quotation marks.
DATEVALUE returns an integer. Apply Format > Number > Date to display that number as a calendar date.
When to Use DATEVALUE
- Convert dates imported as text into usable date values.
- Parse clear month-name or ISO-style date strings.
- Remove the time from a recognized text timestamp.
- Subtract two text dates to calculate elapsed days.
- Prepare imported date text for other formulas.
Convert ISO Date Text to a Date Serial
| Text date | DATEVALUE result |
|---|---|
| 2026-01-15 | 46037 |
| 15 Jan 2026 | 46037 |
| 03/04/2026 | 46085 in en-US |
=DATEVALUE(A2)In the tested en-US sheet, text 2026-01-15 returned 46037. Formatting the result as a date displays January 15, 2026.
A month-name string such as 15 Jan 2026 communicates the intended month clearly. That tested string also returned 46037.
Build a Date From Year, Month, and Day
If D2:F2 contains 2024, 11, and 3, the direct method is:
=DATE(D2,E2,F2)DATE accepts numeric components without creating an intermediate string. Use DATEVALUE when your source is already text.
This original DATEVALUE approach also returned serial 45599 in the tested en-US sheet:
=DATEVALUE(D2&"-"&E2&"-"&F2)Convert Long-Form Dates With Month Names
DATEVALUE can parse recognized month-name text without separate LEFT, MID, or RIGHT formulas:
=DATEVALUE("15 Jan 2026")The expected serial is 46037. Format the result as Date when readers need to see the calendar value rather than its underlying number.
Remove the Time From a Text Timestamp
The tested text 2026-08-22 12:00:00 was recognized in the en-US sheet:
=DATEVALUE(A5)The result was whole-number serial 46256, representing August 22, 2026. Other timestamp layouts may need cleanup before Sheets recognizes them.
If A2 already contains a real datetime value, use =INT(A2) to remove its fractional time component.
Calculate Days Between Two Text Dates
=DATEVALUE("15 Jan 2026")-DATEVALUE("1 Jan 2026")Each DATEVALUE becomes a serial number. Subtracting the start from the end returns 14 elapsed calendar days.
Format the result as Number. Formatting a day count as Date makes it look like an unrelated date near 1900.
What DATEVALUE Accepts in Current Sheets
Google’s documentation says DATEVALUE requires text and that numeric date values return #VALUE!.
In the September 8, 2026 en-US live test, =DATEVALUE(DATE(2026,1,15)) returned 46037 instead. This conflicts with the documented note.
Use DATEVALUE for text inputs and INT for real datetime values. That keeps the formula’s purpose clear without relying on undocumented acceptance.
Invalid text such as 31 Feb 2026 returned #VALUE! in the live test.
See Google’s DATEVALUE reference for documented input and locale behavior.
Other Google Sheets articles you may also like