TIME
Turn three numbers into a time value you can compare and do arithmetic on.
Syntax
TIME(hour, minute, second)| Argument | Required | Description |
|---|---|---|
hour | Required | The hour, 0 to 23. Larger values wrap. |
minute | Required | The minute, 0 to 59. Larger values roll into hours. |
second | Required | The second, 0 to 59. Larger values roll into minutes. |
Examples
Every example below was executed against VisiGrid engine 0.31.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=HOUR(TIME(18,30,0)) | 18 | |
=MINUTE(TIME(12,45,0)) | 45 | |
=SECOND(TIME(0,0,30)) | 30 | |
=HOUR(TIME(0,90,0)) | 1 | Ninety minutes rolls over into one hour thirty. |
=MINUTE(TIME(0,90,0)) | 30 |
A time is a fraction of a day
TIME(18,30,0) is not the text 18:30 — it is 0.77, being 18.5 hours out of
24. That is the same convention Excel uses, and it is what makes time arithmetic
work: adding TIME(1,0,0) to a value adds an hour, and subtracting two times
gives an elapsed fraction you can multiply by 24 to get hours.
=(B1 - A1) * 24 → elapsed hours between two timesRollover is deliberate
TIME(0,90,0) is one hour thirty rather than an error, so you can feed it
computed values without clamping them first:
=TIME(0, A1, 0) → A1 minutes, however many that isHours wrap at 24 rather than rolling into a date — TIME(25,0,0) is 1am, not 1am
tomorrow. If you need the day to advance, add to a DATE
instead.
Reading it back
HOUR, MINUTE and
SECOND take the fraction apart again. Those are the
functions to compare against — testing a raw fraction for equality invites the
rounding you would expect.
Excel compatibility
Matches Excel, including rollover of out-of-range minutes and seconds.