Syntax
=DATEVALUE(date_string)
date_string- Text representing a date, such as "2026-03-15" or "March 15, 2026", or a cell holding it.
DATEVALUE function in Google Sheets
DATEVALUE converts text that looks like a date into a real date (a serial number). Imported data, CSV files and copy-pasted reports often store dates as text, which sort alphabetically and break date maths. DATEVALUE fixes that.
Practice DATEVALUE
Task 1 of 1
These dates were imported as text, so they can't be sorted or used in maths. Convert the first one into a real date in B2.
Tip: while typing a formula, click a cell or drag across cells to insert references. Press Enter to check, and Tab to accept a function suggestion.
DATEVALUE examples
Each example below runs against this sample sheet. The results are calculated by the same engine as the practice grid.
| A | |
|---|---|
| 1 | Imported text |
| 2 | 2026-03-15 |
| 3 | March 15, 2026 |
| 4 | 15 Mar 2026 |
| 5 | 3/15/2026 |
| 6 | next Tuesday |
| Formula | Result | What it shows |
|---|---|---|
=DATEVALUE(A2) | 3/15/2026 | ISO format, year first. |
=DATEVALUE(A3) | 3/15/2026 | A month name works too. |
=DATEVALUE(A4) | 3/15/2026 | Day, short month name, year. |
=DATEVALUE(A2) | 46096 | Without a date format, you see the underlying number. |
=DATEVALUE(A6) | #VALUE! | Text that isn't a date gives #VALUE!. |
Tips and common mistakes
- How text like 3/4/2026 is read depends on the spreadsheet's locale (File › Settings). Year-first text such as 2026-03-04 is the least ambiguous.
- You can tell a text date from a real date by its alignment: real dates sit on the right of the cell, text on the left.
- To convert a whole column at once, wrap it: ARRAYFORMULA(DATEVALUE(A2:A100)).
- Format the result as a date, or it shows as a plain number.
Related functions
Learning step by step? DATEVALUE is covered in Lesson 39: Dates: DATE, YEAR, MONTH, DAY & WEEKDAY of the free course.