Excel TIME Function: Syntax, Examples and Tips

Part of the free Module 5: Excel Formulas and Functions · Function 86 of 105 · Full Excel course

The Excel TIME function builds a valid time from three separate numbers: hours, minutes and seconds. It returns a decimal between 0 and 0.99999 that represents the fraction of a 24-hour day, which Excel displays as a time once the cell is formatted. TIME is the reliable way to create or adjust times inside formulas, because it handles values that overflow their normal range automatically.

Table of Contents

Syntax

=TIME(hour, minute, second)

Arguments

Argument	Required / Optional	Meaning
hour	Required	A number from 0 to 32767. Values above 23 are divided by 24 and the remainder is used, so 27 becomes 3.
minute	Required	A number from 0 to 32767. Values above 59 are converted into hours and minutes, so 90 becomes 1 hour 30 minutes.
second	Required	A number from 0 to 32767. Values above 59 are converted into minutes and seconds.

How TIME works

TIME calculates (hour x 3600 + minute x 60 + second) / 86400 and keeps only the fractional part, which is why the result always lies within one day: =TIME(27,0,0) returns 03:00, not 27 hours. Decimal arguments are truncated to integers, so TIME(9,30.9,0) is 09:30. Any negative argument returns #VALUE!. The result is a plain number; apply a time format such as h:mm or h:mm AM/PM to display it, and add it to a date serial number to build a full timestamp.

Step-by-step example: shift start and end times

Enter shift start hours in column A (for example 9) and minutes in column B (for example 30).
In C2 enter =TIME(A2,B2,0). The cell shows 0.395833; format it as h:mm to see 09:30.
In D2 enter =C2+TIME(8,45,0) to add an 8-hour 45-minute shift. The result is 18:15.
Fill both formulas down. For a night shift starting at 22:00, D2 shows 06:45 because TIME arithmetic wraps at midnight; use =MOD(D2-C2,1) when calculating durations across midnight.

Practical use cases

1. Add minutes to a time

=A2+TIME(0,45,0)

2. Combine separate date and time cells into a timestamp

=A2+TIME(B2,C2,0)

3. Convert decimal hours to a time

=TIME(INT(A2),(A2-INT(A2))*60,0)

Turns 7.75 into 07:45. Dividing by 24 (=A2/24) gives the same result more briefly.

4. Round a timestamp down to the hour

=TIME(HOUR(A2),0,0)

5. Time from a four-digit text value such as 1430

=TIME(LEFT(A2,2),RIGHT(A2,2),0)

Common mistakes and tips

Durations over 24 hours: TIME wraps, so TIME(30,0,0) is 06:00. For durations use plain arithmetic (30/24) and the format [h]:mm.
Result shows a decimal: apply a time format; the value is correct.
Negative components: TIME(-1,0,0) returns #VALUE!. Subtract a TIME value from another time instead.
Decimal minutes: the argument is truncated. Convert fractional minutes to seconds first.
Google Sheets difference: Sheets accepts negative arguments and wraps them; Excel does not.

TIME compared with TIMEVALUE, typing times and division by 24

TIME assembles a time from numbers; TIMEVALUE parses a time written as text such as “2:30 PM”. Use TIME when the parts are already numeric or extracted with LEFT and RIGHT, and TIMEVALUE when a whole text string must be converted. Typing a time directly into a formula as “9:30” also works, because Excel converts the text automatically, but it depends on regional settings, whereas TIME(9,30,0) is unambiguous everywhere. Dividing by 24 is the shortest way to turn decimal hours into a time and, unlike TIME, it keeps durations above 24 hours intact. Together with HOUR, MINUTE and SECOND, which take a time apart, TIME completes the set of tools for time manipulation.