TIMEVALUE

What it does

TIMEVALUE(time_text)

TIMEVALUE converts text that reads as a time into the fraction of a day it denotes — the same kind of value TIME constructs from three numbers, and the fractional counterpart to what DATEVALUE produces from date text.

It is the exact mirror of DATEVALUE: where DATEVALUE parses the date part of a string and discards any time, TIMEVALUE parses the time part and discards any date. TIMEVALUE("1/1/2024 13:00") is 13:00 as a fraction, with the date thrown away.

The result is therefore always in [0, 1), and the caveats about representability on TIME apply unchanged: 1/86400 is not exactly representable, so a parsed time is generally a nearby double rather than the exact value.

Arguments

TIMEVALUE(time_text) — one required argument.

time_text — text representing a time, or a reference to a cell containing such text. The documented examples are of the "6:45 PM" and "18:45" shape.

The misunderstood case is the same one DATEVALUE has: passing a cell that already holds a real time. A cell holding a time holds a number, and this function exists for text that Excel did not convert on entry — typically imported or concatenated data.

Try it

Quirks

Result and edge cases

Returns a Number in [0, 1).

• Any date part is discarded, so a full timestamp string yields only its time.
• Values at or above 24 hours have no representation in the result's range. Whether TIMEVALUE("25:00") errors, wraps to 01:00 (as TIME's documented modulo rule would), or is simply not accepted by the parser is not documented and not checked here. It is the most interesting open question about this function, because the two plausible answers come from two different parts of the system: the parser's grammar and the constructor's arithmetic.
• Fractional seconds — whether "12:00:00.5" parses, and whether the fraction survives, is undocumented and unverified.
• Whitespace, AM/PM spelling and separators are part of the same undocumented, locale-scoped accepted set that DATEVALUE has.

Errors

As documented:

• #VALUE! when time_text does not represent a valid time.

An error value passed in propagates in the ordinary way. Nothing here overrides the engine rules in coercion and lifting.

How to implement

Notes for implementers

1. Locale is an input. A parser with a hard-coded HH:MM:SS grammar implements one configuration of this function, not the function.
2. Decide, and document, the ≥24-hour policy. The TIME constructor's documented modulo rule is a tempting default, but TIMEVALUE is a parser and parsers usually reject rather than wrap. Neither behaviour can be claimed as Excel's without observation.
3. Preserve the discard rule exactly. Parsing a full timestamp and returning frac of the whole serial is not the same as parsing the time part, once the date part is out of range or ambiguous.
4. Do not claim compatibility from an inferred grammar. State the forms accepted; do not state that they are the forms Excel accepts.

Rust code

13 timevalue_fn "TIMEVALUE" (1, 1) [V "time_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 - 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

• DATEVALUE — the mirror. DATEVALUE(t) + TIMEVALUE(t) is the standard way to turn one timestamp string into one serial, and it works precisely because each function discards what the other keeps.
• TIME — the deterministic, locale-free alternative when the components are already numbers. Prefer it wherever the input is not literally text.
• HOUR, MINUTE, SECOND — the decomposition applied to whatever TIMEVALUE produces; a natural round-trip test.
• The VALUE function — the general text-to-number conversion, whose accepted set overlaps this one in ways the documentation does not describe.

Named variants

No named variants are recorded yet.