TIME

What it does

TIME(hour, minute, second)

TIME is the constructor for the fractional half of a date serial, as DATE is the constructor for the integer half. It takes three numbers and returns the fraction of a day that they name:

TIME(h, m, s) = frac( (3600·h + 60·m + s) / 86400 )

That is the whole definition, and reading it as one expression rather than three separate rules explains everything Microsoft's page documents piecewise. The page says an hour above 23 "will be divided by 24 and the remainder will be treated as the hour value", that a minute above 59 "will be converted to hours and minutes", and that a second above 59 "will be converted to hours, minutes, and seconds". All three are the same statement: the three components are summed into seconds, and only the position within the day survives.

Two properties follow immediately, and both matter:

1. The result is always in [0, 1). Microsoft documents the range as 0 to 0.99988426, which is 86399/86400 — one second short of a full day. TIME cannot return 1, and therefore cannot express "24:00" or any duration longer than a day.
2. TIME is a clock, not a stopwatch. TIME(25,0,0) is 01:00, not "25 hours". To express a duration you do the division yourself: 25/24. This is the single most common misuse of the function.

Like DATE, TIME is a normalizing constructor rather than a validator: it does not reject 90 minutes, it carries them.

Arguments

TIME(hour, minute, second) — all three required.

The 32767 ceiling is the documented upper bound on each argument. Microsoft's page does not state what happens above it; this Handbook has not checked, and #NUM! is a guess, not a finding.

Negative components are the genuinely under-documented case. The page gives ranges starting at 0 and says nothing about negatives, yet TIME(1,-30,0) is a perfectly well-formed call. Whether it yields 00:30, an error, or a wrapped value is unknown here — see below.

Try it

Quirks

Result and edge cases

Returns a Number in [0, 1). Excel will normally show it in a time format; that formatting is host-side adaptation and not part of the value (see the call pipeline, stage 4).

Because the result is a binary double and the natural denominator is 86400 — which is not a power of two — almost every time value is inexact. TIME(0,0,1) is not exactly 1/86400. This is not a defect of TIME; it is the same representability fact that makes =A1-B1 on two timestamps produce values like 0.20833333333333337. It becomes visible when a time is compared for equality, or fed back through HOUR/MINUTE/SECOND.

Argument coercion is the ordinary scalar behaviour of coercion and lifting: numeric text converts, logicals become 1 and 0, an error argument propagates.

Errors

Microsoft's TIME page documents overflow handling but does not enumerate error conditions. What can be said honestly:

• #VALUE! from an argument that will not convert to a number is the ordinary coercion outcome, not a TIME-specific rule.
• An error value passed as any argument propagates.
• Whether arguments above the documented 32767 ceiling, or negative arguments, produce #NUM! is not documented on that page and not verified here.

How to implement

Notes for implementers

1. Sum first, wrap once. Computing h mod 24 before adding minutes and seconds gives the right answer for the documented examples but is fragile; summing to total seconds and taking one modulus is both simpler and closer to what the documentation describes.
2. Decide the negative policy explicitly. Whichever behaviour Excel turns out to have, an implementation that falls into it accidentally through language-defined % semantics is a latent divergence. Rust and C % truncate toward zero; Python's % floors. They disagree exactly on the undocumented case.
3. Do not round the result. The publication profile for this function is the plain IEEE-754 double (see the call pipeline); the visible "0.99988426" in the documentation is a display of the maximum, not a rounding rule.

Rust code

2 time_fn "TIME" (3, 3) [V "hour", V "minute", V "second"] |s, a, sv| {
    let (h, m, sec) = (num(s, a, 0)?, num(s, a, 1)?, num(s, a, 2)?);
    out(s, k::time(h, m, sec))
}

pub(crate) fn time(hour: f64, minute: f64, second: f64) -> Result<f64, W> {
    if !hour.is_finite() || !minute.is_finite() || !second.is_finite() {
        return Err(W::Num);
    }
    let (hour, minute, second) = (hour.trunc(), minute.trunc(), second.trunc());
    if hour.abs() > 32767.0 || minute.abs() > 32767.0 || second.abs() > 32767.0 {
        return Err(W::Num);
    }
    let total_seconds = hour * 3600.0 + minute * 60.0 + second;
    if total_seconds < 0.0 {
        return Err(W::Num);
    }
    Ok((total_seconds / 86_400.0).rem_euclid(1.0))
}

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

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Alternatives

Relationships

• DATE — the integer-part constructor. DATE(...) + TIME(...) is the idiomatic way to build a full timestamp.
• HOUR, MINUTE, SECOND — the decomposition. They are the left inverses of TIME on the in-range domain, modulo the representability caveat above.
• TIMEVALUE — reaches the same fractions from text, and is locale-dependent where TIME is not.
• NOW — supplies a live timestamp whose fractional part is what TIME would construct.
• Confusable. TIME and the TEXT function with a time format code are routinely interchanged in advice; TEXT produces text, TIME produces a number.

Named variants

No named variants are recorded yet.