DATEVALUE

What it does

DATEVALUE(date_text)

DATEVALUE converts text that reads as a date into the corresponding date serial. Microsoft's description of the argument is worth reading literally: it accepts "text that represents a date in an Excel date format" — that is, the function's domain is defined by Excel's date formats, not by any fixed grammar. "1/30/2008" and "30-Jan-2008" are the documented examples.

That phrasing is the whole story of this function. Its domain is:

• clock-dependent — Microsoft documents that when the year is missing from date_text, the function "uses the current year from your computer's system clock". A parse of "3/4" therefore returns a different answer in different years, which makes DATEVALUE non-deterministic in a way the category's other conversions are not;
• format-set-dependent — the accepted forms are those Excel would accept when typing into a cell, a set that is not enumerated in the documentation.

Together, those three make DATEVALUE the least specifiable function in this assignment. The mathematics is trivial; the specification is the problem.

Microsoft also documents two firm rules:

• Time information in date_text is ignored. DATEVALUE("1/1/2024 13:00") yields a whole serial; the fraction is discarded. TIMEVALUE is the mirror image.
• The result must fall in range. Under the default Windows date system, the date must lie between 1 January 1900 and 31 December 9999; outside that, #VALUE!.

Arguments

DATEVALUE(date_text) — one required argument.

date_text — "text that represents a date in an Excel date format, or a reference to a cell that contains text that represents a date in an Excel date format".

Two argument-position subtleties matter more here than the singular argument count suggests:

1. A cell that already contains a real date is the wrong input. If the cell holds a date, it holds a number, and DATEVALUE is being asked to parse a number. The function exists for text that Excel did not convert on entry — typically imported data.
2. The year-omitted case is a live behaviour, not a degenerate one. Microsoft documents it explicitly, and it is the reason a workbook built in one year can produce different values when recalculated in the next.

Try it

Quirks

Result and edge cases

Returns a Number: a whole date serial with no time component.

• Leading and trailing whitespace, separators, and month-name spellings are all part of the undocumented accepted set. The Handbook's coercion chapter already flags the broader text-to-number recognizer as an open area with a locale-dependent surface that has been probed only at the edges; DATEVALUE is where that open area is most exposed.
• Two-digit years have a pivot convention that the documentation does not state. Through VALUE, in en-ZA year-month-day order, FN-VALUE-002 reads "24/2/1" as 2024 and "1/2/1" as 2001. Where the window turns over has not been read.
• A date before 1 January 1900 is documented as #VALUE! rather than #NUM! — worth noting, because out-of-range serials elsewhere in the category give #NUM!. Here the failure is a parse failure, and it takes the parse failure's error value.

Errors

As documented:

• #VALUE! when date_text does not represent a date Excel can parse, or when the resulting date falls outside the representable range.

An error value passed in propagates in the ordinary way.

How to implement

Notes for implementers

1. The grammar is host state, not a constant. An implementation must take the locale (or an explicit format profile) as an input. A hard-coded MM/DD/YYYY parser is not an implementation of this function; it is an implementation of one of its configurations.
2. The system clock is an input too. The year-omitted rule makes this function time-dependent — a property it shares only with NOW and TODAY in this category, and unlike them it is not declared volatile. A cached result of DATEVALUE("3/4") can therefore become quietly wrong at a year boundary.
3. Fail loudly on ambiguity. Where the configured format admits two readings, there is no correct silent choice.
4. Do not attempt an excel-bitexact claim from a grammar you inferred. Until the accepted set is characterized under a named locale, an implementation can only state which forms it accepts, not which forms Excel accepts.

Rust code

12 datevalue_fn "DATEVALUE" (1, 1) [V "date_text"] |s, a, sv| {
    let reading = super::text_format::read_value(s, a, 0)?;
    match reading {
        crate::value_text::Reading::DateTime(serial) => s.number(serial.floor()),
        crate::value_text::Reading::Number(_) => fail(W::Value),
    }
}

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• DATE — the deterministic alternative. Every piece of advice in this category amounts to "use DATE, not text", and this function is why the advice exists.
• TIMEVALUE — the mirror: parses the time part and ignores the date part, where DATEVALUE parses the date part and ignores the time part. Composing them — DATEVALUE(t) + TIMEVALUE(t) — is the documented-behaviour way to parse a full timestamp from one string.
• DAYS — documented to route its text arguments through DATEVALUE, which imports this function's locale dependence into an otherwise numeric function.
• The VALUE function — the general text-to-number conversion, which also accepts date text in practice; the relationship between the two accepted sets is not documented, and it is checked here only for digit scripts (FN-VALUE-001) and the digits of year, month and day (FN-VALUE-002).
• Confusable. DATEVALUE and DATE are interchanged constantly in advice, and they have nothing in common but a name and a return kind.

Named variants

No named variants are recorded yet.