DATEVALUE in Excel: Convert Date Text and Check Regional Assumptions
Convert Excel date text with explicit year and locale assumptions, verify serials, and separate date-only parsing from full timestamps.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: DATEVALUE converts recognized date text into an Excel date serial. Its interpretation can depend on system date settings. It discards any time component, and a missing year is filled from the current year of the computer clock.
Availability and version history
- Introduced: first introduction version is not established by the official history reviewed; the current product list is not a launch record.
- Availability: official help lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016.
- Checked: October 14, 2026; official DATEVALUE reference. Documentation and independently modeled examples; no native Excel execution or broader platform certification.
Syntax and input contract
=DATEVALUE(date_text)
The required input is text representing a date in a format Excel recognizes, or a reference to such text. Apply a date format to see the result as a calendar date; General or Number displays the underlying serial.
The examples below assume English month names are recognized and the workbook uses the 1900 date system. They are documentation-based expectations under that scope, not a promise that every regional setting accepts every displayed string. DATE is preferable when year, month and day are already separate numeric fields.
Example 1: parse a complete, explicitly named date
=DATEVALUE("14-Oct-2026")
Expected serial: 46309, representing October 14, 2026 in the stated 1900 system. Formatting that number as yyyy-mm-dd displays 2026-10-14; it does not change the stored value.
For an independent control, =DATE(2026,10,14) represents the same date. The mathematical serial check uses the established modern 1900-system offset, not a claim about every historical boundary near 1900.
Example 2: convert a small imported column
In A2:A4 enter the text values 14-Oct-2026, 16-Oct-2026 and 01-Nov-2026. Prefix each with an apostrophe during entry if necessary to retain text. The apostrophe is an entry marker, not part of the intended date string.
Enter =DATEVALUE(A2) in B2 and fill down. Expected serials are 46309, 46311 and 46327. With a date format, these display the corresponding October 14, October 16 and November 1 dates.
=B3-B2
=B4-B2
Expected differences are 2 and 18 calendar days. These checks confirm the converted chronology without relying on how the original text sorts alphabetically. Keep the original imported text alongside the parsed value until the conversion is reviewed.
Advanced example: time text is intentionally removed
Under the same English-date interpretation:
=DATEVALUE("14-Oct-2026 18:30")
Expected result remains 46309. DATEVALUE ignores the time component; it does not produce 46309.770833333. Displaying the result with hours and minutes would show midnight, not the supplied 18:30.
If the full timestamp is required, explicitly combine its date and time interpretations:
=DATEVALUE("14-Oct-2026 18:30")+TIMEVALUE("14-Oct-2026 18:30")
Expected serial: approximately 46309.770833333, since 18.5 hours is 18.5/24 of a day. TIMEVALUE has a separate contract and ignores the date component. Both parsers still need to recognize the chosen text format.
Advanced check: an omitted year makes an import time-dependent
=DATEVALUE("14-Oct") uses the current year from the computer clock. If that year is 2026, its expected date is October 14, 2026; if it is 2027, it is October 14, 2027. Those modern 1900-system serials are 46309 and 46674.
This is a documented default, not a reliable way to recover a missing historical year. A saved import can change meaning when recalculated in another year. Supply the actual year when records need to remain reproducible.
The vendor sample for a yearless July date contains an inconsistent stated year/result pair. This guide follows the explicit current-year rule and computes its own complete-year controls instead of copying that sample serial.
Regional ambiguity and explicit components
A string such as 03/04/2026 can suggest March 4 or April 3 under different date conventions. This guide does not assign it one universal result. System date settings are part of the documented parsing context.
If an import specification already identifies the year as 2026, month as 4 and day as 3, construct =DATE(2026,4,3) rather than assembling an ambiguous slash string. That avoids guessing a regional order, although DATE itself normalizes some out-of-range numeric components and is not complete validation.
Errors and practical limits
Microsoft states that, under the default Windows date system, date_text must represent January 1, 1900 through December 31, 9999; a date outside that interval has documented #VALUE!. A complete mixed-type, invalid-text, non-English-month and alternate-date-system test matrix is not established here.
Do not convert every parse failure to zero: a zero serial can be misread as an actual date. Retain the source text and flag an unresolved conversion so the missing assumption can be corrected.
References and verification
Expected results are independent calendar/arithmetic models or explicitly documented rules, not captured native output. Examples use English function names and comma separators. Input dates and schedules are fictional.