DATEVALUE in Google Sheets: Parse Date Text and Keep Numeric Dates Separate

• Introduced: first introduction date is not established by the official history reviewed; no desktop release number is inferred.
• Availability: documented in the rolling Google Sheets service; these examples use the web worksheet workflow.
• Checked: October 14, 2026; official DATEVALUE reference. Documentation and independent models, with no native Google Sheets execution or wider client certification.

=DATEVALUE(date_string)

Supply quoted text or a reference holding text in a recognized date format. Google says recognition depends on regional and language settings. A successful parse in one shared spreadsheet is not proof that every imported slash-formatted date is unambiguous.

The return value is an integer. Format it as a date to display a calendar label, or keep a numeric format to inspect its serial. DATE constructs a date from numeric components instead of parsing a string.

=DATEVALUE("2026-10-14")

In a spreadsheet recognizing the year-month-day pattern illustrated by Google’s own reference, the expected calendar date is October 14, 2026. Using Sheets’ documented December 30, 1899 date base, the independently calculated serial is 46309. The Google N reference documents that base.

This is a documentation-based parsing expectation, not a captured native locale test. First verify that the pattern is understood in the intended spreadsheet; a number format can show 46309 as a date without changing its value.

Enter these formulas in A2:A4 to create text explicitly:

In B2 enter =DATEVALUE(A2) and fill down. The expected gaps B3−B2 and B4−B2 are 2 and 18 days. Keep the original strings so a later reviewer can inspect what was parsed.

Typing an unquoted date directly into a cell may cause entry-time conversion. If that already produced a numeric date, the same cell is no longer the text input required by DATEVALUE.

In D2 enter =DATE(2026,10,14). Then compare:

=ISNUMBER(D2)
=DATEVALUE(D2)

Expected results are TRUE and the documented #VALUE! error. D2 already contains the numeric date, even when its display looks identical to the original string.

By contrast, =DATEVALUE("2026-10-14") is a text-parse request. Use ISNUMBER to inspect the input’s current type before deciding which operation is needed. A blanket DATEVALUE wrapper can break dates that were already valid.

With the parsed value in B2:

=AND(YEAR(B2)=2026,MONTH(B2)=10,DAY(B2)=14)

Expected flag: TRUE. Checking the actual year, month and day is more informative than merely checking that a numeric result was returned.

For example, the text "04/05/2026" could denote April 5 or May 4 under different conventions. Those two possible dates are 29 days apart. This guide deliberately does not select one interpretation without a stated locale. Preserve the original string and compare against the intended components.

For a controlled column whose only allowed states are an already numeric date or a recognized date string:

=IF(ISNUMBER(A2),A2,DATEVALUE(A2))

If A2 is =DATE(2026,10,14), the selected result is its existing date value. If A2 is ="2026-10-14", the selected result is the parsed date. Both expected numeric results are 46309 in these controls.

This is not a complete validator: an arbitrary numeric 12 would also pass through unchanged. Add a separately defined permitted date range if that distinction matters. Do not replace an unexplained parse error with a plausible date or zero merely to make the report look complete.

The dedicated reference explicitly settles numeric-input #VALUE!, but it does not provide a complete matrix for malformed strings, missing-year conventions, time-bearing input or historical endpoints. Those cases remain unverified here. TIMEVALUE is a different parser for time text, and numeric date construction belongs to DATE.

Use File → Settings → General to inspect the shared spreadsheet locale. Any change affects collaborators too. The worked year-month-day controls should be tested in the actual file before applying the same conversion to a large import.

• Google DATEVALUE reference.
• Editorial testing standards.

Examples use fictional inputs, English function names and comma argument separators. Expected outputs are independently modeled from the cited rules, not native Sheets captures. Check the spreadsheet locale when reproducing punctuation-sensitive formulas.