Excel TIME Function: Build a Time From Hours, Minutes and Seconds
Assembles a time value from separate hour, minute and second numbers.
TIME takes three numbers and returns a time Excel understands. It is the counterpart to DATE, and it exists for the same reason: times arrive split across columns, or need building from a calculation rather than typed.
The thing to know before anything else is that Excel stores a time as a fraction of a day. Midday is 0.5, six in the morning is 0.25, and one hour is 1/24. Every oddity below follows from that single fact, and none of it makes sense without it.
Values outside the normal range wrap rather than erroring, which is usually helpful and occasionally not. =TIME(25, 0, 0) returns 01:00, because 25 hours is one day and one hour and TIME keeps only the time part. That means TIME cannot represent a duration longer than 24 hours — for those you add raw numbers and format the result as [h]:mm.
Syntax
=TIME(hour, minute, second)Arguments
hourRequired- 0 to 23. Anything larger wraps: 25 becomes 1, because the day part is discarded.
minuteRequired- 0 to 59 normally. Larger values roll into hours — 90 becomes 1:30.
secondRequired- 0 to 59 normally, rolling into minutes the same way.
The example data
Headers in row 1, data in A2:D5. Columns B and C hold real times.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Staff | Clock in | Clock out | Break (mins) |
| 2 | Alice Moreau | 08:15 | 16:45 | 30 |
| 3 | Bruno Santos | 13:00 | 21:30 | 45 |
| 4 | Chen Wei | 22:00 | 06:00 | 60 |
| 5 | Dana Okafor | 09:30 | 14:00 | 0 |
Worked examples
=TIME(9, 30, 0)Result: 09:30
A time assembled from three numbers rather than typed as text.
=TIME(0, D2, 0)Result: 00:30
Turning a break of 30 minutes into a real time value, so it can be subtracted from a shift length.
=C2 - B2 - TIME(0, D2, 0)Result: 08:00
The shift calculation: clock out minus clock in minus the break, all as time values.
=TIME(25, 0, 0)Result: 01:00
The wrap. 25 hours is a day and an hour, and TIME keeps only the remainder — which is why it cannot hold a duration.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
Result shows as 0.395833
Why it happens: The cell is formatted as General. A time is a fraction of a day underneath.
How to fix it: Format the cell as a time. The value is already correct.
A total over 24 hours resets to zero
Why it happens: The standard h:mm format only shows the time-of-day part, so 30 hours displays as 6:00.
How to fix it: Use the custom format [h]:mm — the square brackets tell Excel to let hours accumulate past 24.
Negative time shows as ####
Why it happens: Excel cannot display a negative time in the 1900 date system, which happens on an overnight shift where clock out is earlier than clock in.
How to fix it: Add a day when the end is earlier: =C4 - B4 + (C4 < B4).
#NUM!
Why it happens: An argument is negative.
How to fix it: TIME accepts values above the normal range but not below zero.
Tips worth knowing
- Multiply a time by 24 to get decimal hours, which is what payroll usually wants: =(C2-B2)*24 gives 8.5.
- [h]:mm is the format for durations; h:mm is the format for clock times. Using the wrong one is the most common time bug.
- For an overnight shift, adding (end < start) adds exactly one day when the comparison is TRUE.
- TIME wraps at 24 hours, so build durations by adding numbers rather than by calling TIME.
Frequently asked questions
Why does my time total reset after 24 hours?
The h:mm format shows only the time-of-day part, discarding whole days. Apply the custom number format [h]:mm instead — the brackets tell Excel to keep accumulating hours rather than wrapping at midnight.
How do I convert a time to decimal hours?
Multiply by 24. A time is stored as a fraction of a day, so 8:30 is 0.354166, and multiplying gives 8.5 — the figure a timesheet or payroll calculation needs.
How do I handle a shift that runs past midnight?
Add one day when the end time is earlier than the start: =end - start + (end < start). The comparison returns TRUE, which is 1, so exactly one day is added only when needed.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.