Excel TIME, HOUR, MINUTE & SECOND — Read and Build the Fraction Behind the Clock

TL;DR — Excel stores a clock time as a fraction of a 24-hour day: midnight is 0, 6:00 AM is 0.25, noon is 0.5, 6:00 PM is 0.75. HOUR, MINUTE and SECOND read the whole-number pieces out of that fraction; TIME(hour, minute, second) builds a fraction from pieces and wraps past midnight (=TIME(25,0,0) → 1:00 AM). TIMEVALUE turns a text time like "2:30 PM" into the fraction 0.6042. The one thing to burn in: these functions read the clock face, not elapsed time — HOUR of a 30-hour duration is 6, not 30.

=HOUR("6:00 AM")          ' -> 6
=MINUTE("14:37")          ' -> 37
=SECOND("14:37:20")       ' -> 20
=TIME(14, 30, 0)          ' -> 0.604167  (format as time -> 2:30 PM)
=TIME(25, 0, 0)           ' -> 0.041667  (wraps -> 1:00 AM)
=TIMEVALUE("2:30 PM")     ' -> 0.604167  (fraction of a day)

You have a column of timestamps and you need the hour for a pivot, or you need to build 08:30 from two input cells, or a time arrived as text and won't cooperate. All four functions do one job each — and all four make sense the moment you hold the picture that a time is just a number between 0 and 1. This guide covers that model, the wrap-around behaviour that surprises people, and the line where these functions stop being the right tool and plain arithmetic takes over.

What you'll learn

The mental model: a clock time is a fraction of a day (0 to 1)
HOUR / MINUTE / SECOND read the clock face — and why that isn't elapsed time
TIME builds a fraction from pieces, and wraps at 24 hours
TIMEVALUE rescues a time that arrived as text
The judgment call: when to read pieces vs. when to do arithmetic

The mental model: a time is a fraction of a day

Type 6:00 AM into a cell and Excel stores 0.25 — a quarter of the way through a 24-hour day — then dresses it up with a time format. That single fact explains every function below. Because the underlying value is a fraction, HOUR doesn't "extract" anything magical: it multiplies the fraction back out and hands you the whole hours.

' A2 shows 6:00 AM; the number underneath is 0.25
=A2 * 24        ' -> 6      (0.25 of a day = 6 hours)
=HOUR(A2)       ' -> 6      (same answer, as a clean integer)

Hold that picture: the functions operate on the fraction; the time format is only a costume painted over it. Everything that follows — the wrap-around, the "why is my duration wrong", the text-time trap — is a consequence of the fraction underneath.

HOUR, MINUTE and SECOND read the clock face

The three readers pull the corresponding piece out of a time value and always return a whole number in a fixed range: HOUR returns 0–23, MINUTE and SECOND return 0–59.

=HOUR("14:37:20")    ' -> 14
=MINUTE("14:37:20")  ' -> 37
=SECOND("14:37:20")  ' -> 20

They work on a real timestamp too — HOUR(NOW()) gives the current hour — because a date-time is a serial day-count plus a time-fraction, and HOUR simply reads the fraction part.

Here is the trap that catches everyone, and it follows straight from "clock face": HOUR reads the time of day, not how long something lasted. If two events are 30 hours apart, the duration is 1.25 (one and a quarter days), and:

' D2 holds a duration of 30 hours (the number 1.25)
=HOUR(D2)     ' -> 6    NOT 30  (30 hours = 1 day + 6 hours; HOUR sees the 6)

HOUR throws away the whole days and reports the clock reading. To get 30, you want elapsed hours, which is arithmetic — D2 * 24 — not HOUR. That is the whole reason converting time to decimal hours is a separate job from reading the clock.

TIME builds a fraction — and wraps at midnight

TIME(hour, minute, second) does the reverse: it takes three numbers and assembles the day-fraction.

=TIME(8, 30, 0)     ' -> 0.354167   (format as time -> 8:30 AM)
=TIME(14, 30, 0)    ' -> 0.604167   (-> 2:30 PM)

The behaviour worth memorising is what happens when the pieces overflow. TIME rolls over rather than erroring — minutes past 59 carry into hours, and hours past 23 wrap around the clock (it effectively takes the result mod 24 hours):

=TIME(8, 90, 0)     ' -> 0.395833   (8h + 90min -> 9:30 AM)
=TIME(25, 0, 0)     ' -> 0.041667   (25:00 wraps -> 1:00 AM)

That roll-over is a feature when you want "add 90 minutes to a start time" — but a trap when you expected TIME(25,0,0) to give you a 25-hour duration. It can't: TIME only ever produces a value between 0 and 1, i.e. a point on the clock, never an elapsed span of a day or more. For durations that can exceed 24 hours, build them with arithmetic and show them with the [h]:mm format — see summing time over 24 hours.

TIMEVALUE rescues a time stuck as text

When a time arrives from an export as text — left-aligned in the cell, won't subtract, won't sort — there is no fraction underneath, so HOUR and arithmetic both fail. TIMEVALUE parses the string into the fraction:

' A2 holds the TEXT "2:30 PM"
=A2 * 24            ' -> #VALUE!    (can't multiply a string)
=TIMEVALUE(A2)      ' -> 0.604167   (now a real fraction; *24 gives 14.5)
=HOUR(TIMEVALUE(A2))' -> 14

It's the time-of-day twin of DATEVALUE. If your source strings carry both a date and a time, add the two parsers: DATEVALUE(LEFT(...)) + TIMEVALUE(MID(...)) rebuilds a full timestamp. See DATEVALUE & TIMEVALUE for the combined-parsing pattern.

The judgment call

"What's on the clock?" — the hour for a pivot, the minute for a label → HOUR / MINUTE / SECOND. They give clean integers you can group and compare.
"How long did it take?" — a duration, especially one over 24 hours → don't use HOUR; subtract and multiply by 24 for decimal hours, or use [h]:mm to display it.
Building a time from parts → TIME(h, m, s), remembering it wraps at midnight. Use the wrap deliberately (add minutes) and never to represent a long duration.
Rounding to the nearest 15 minutes → work on the fraction with MROUND: =MROUND(A2, TIME(0,15,0)). Reading pieces with MINUTE and stitching them back is the long way round.
A time that won't do math → it's text; wrap it in TIMEVALUE once, at the import boundary, rather than sprinkling it through every formula.

How ExcelMaster helps

Time formulas fail quietly: the number is often right and only the format is wrong, or a duration silently loses its whole days. ExcelMaster reads the intent behind the request — "get the hour", "build a start time", "these times came in as text" — and writes the formula that fits, including the parts people forget: wrapping text times in TIMEVALUE, choosing TIME vs. arithmetic for durations, and applying the right number format so the result you see matches the number underneath. You describe the clock; it handles the fraction.

Frequently asked questions

Why does HOUR return 6 for a 30-hour duration?

Because HOUR reads the clock face, not elapsed time. Thirty hours is one full day plus six hours (the number 1.25); HOUR discards the whole day and reports the 6. For elapsed hours use arithmetic — =duration * 24 gives 30.

How do I add 90 minutes to a time in Excel?

Add a TIME value: =A2 + TIME(0, 90, 0). TIME rolls the 90 minutes over into 1:30, so an 8:00 AM start becomes 9:30 AM. Adding 90/1440 works too, since a minute is 1/1440 of a day.

How do I get the time from a text string like "2:30 PM"?

Wrap it in TIMEVALUE: =TIMEVALUE("2:30 PM") returns 0.6042, a real fraction of a day. You can then read pieces with HOUR/MINUTE or multiply by 24 for decimal hours.

What is the difference between TIME and TIMEVALUE?

TIME(h, m, s) builds a time from three numbers. TIMEVALUE("...") parses a text string into the same kind of fraction. Use TIME when you have the parts as numbers; use TIMEVALUE when a time arrived as text.

How do I round a time to the nearest 15 minutes?

Round the underlying fraction: =MROUND(A2, TIME(0,15,0)). Because TIME(0,15,0) is 15 minutes as a day-fraction, MROUND snaps the value to the nearest quarter hour without you having to take the clock apart.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-25.