TIME Function

The TIME function creates a valid Excel time using the given values for hour, minute, and second. Like all Excel time, the result is a number that represents a fractional day. The TIME function will only return time values up to one full day, between 0 (zero) to 0.99999999, or 0:00:00 to 23:59:59.  To see results formatted as time, apply a time-based number format. 

The main benefit of the TIME function is the convenience of entering a time with separate values for hours, minutes, and seconds. It is analogous to the DATE function, which creates dates with separate values for year, month, and day.

Basic example

=TIME(3,0,0) // 3 hours
=TIME(0,3,0) // 3 minutes
=TIME(0,0,3) // 3 seconds
=TIME(8,30,0) // 8.5 hours

Time formatting

The TIME function returns a valid time as a number, but the way Excel displays the result depends on number formatting. Excel can display that result as time with AM/PM suffix, a 24-hour time like 17:30:00, or as a regular number. You can see how these formats compare in the screen below, where the number formatting applied to columns F, G, and H is as follows:

h:mm AM/PM
h:mm:ss
0.0

Number formatting affects the display of time only, not the underlying value.

Larger units

The TIME function can interpret units in larger increments. For example, both of the formulas below return 2 hours:

=TIME(0,120,0) // 2 hours
=TIME(0,0,7200) // 2 hours

However, TIME will not handle numeric inputs larger than 32,767. For example, even though there are 86,400 seconds in a day, the following formula (which represents 12 hours) will fail with a #NUM! error:

=TIME(0,0,43200) // returns #NUM! 

Time over 24 hours

It's important to understand that TIME only returns results up to 24 hours. When the total time exceeds 24 hours, the TIME function will "reset" to zero.

=TIME(12,0,0) // returns 0.5 (12 hours)
=TIME(18,0,0) // returns 0.75 (18 hours)
=TIME(24,0,0) // returns 0 (0 hours)
=TIME(36,0,0) // returns 0.5 (12 hours)
=TIME(48,0,0) // returns 0 (0 hours)

Notice TIME returns 0 for 24 hours and 48 hours above. In this way, the behavior of the TIME function is similar to a 24-hour clock that resets at midnight. To create time durations greater than 24 hours, you can use a manual formula to convert hours, minutes, and seconds to Excel time like this:

=(hours/24)+(minutes/1440)+(seconds/86400)

The result from the formula above is the same as with the TIME function for time durations up to 24 hours. For time durations over 24 hours, this formula will continue to work correctly while the TIME function will reset to zero at each 24-hour interval. For example, for a time duration of 72 hours, TIME returns zero:

=TIME(72,0,0) // returns 0

However, the alternate formula above will correctly return 3 days:

=(hours/24)+(minutes/1440)+(seconds/86400)
=(72/24)+(0/1440)+(0/86400)
=(3)+(0)+(0)
=3

When formatted with the custom number format [h]:mm:ss. Excel will display 72:00:00.

This formula is also useful for creating time that includes fractional seconds.

Negative inputs

Although the TIME function will return an error if the final result is negative, you can supply units as negative numbers so long as the result is positive. For example:

=TIME(12,-3,0) // returns 09:00:00
=TIME(24,-1,0) // returns 23:59:00
=TIME(3,-120,0) // returns 01:00:00

TIME function limitations

There are two limitations to the TIME that you should be aware of. First, TIME will only accept whole numbers for hours, minutes, and seconds. If you supply decimal values, the decimal portion of the number will be discarded. For example, both formulas below return 12 hours even though the second formula provides 12.5 for hours:

=TIME(12,0,0) returns 12 hours
=TIME(12.5,0,0) returns 12 hours

Second, TIME only supports positive time. If you provide inputs that would create a negative time, TIME will return a #NUM! error:

=TIME(-6,0,0) // returns #NUM! 

Notes

• When the total time reaches 24 hours, the TIME function will "reset" to zero. 
• The largest number that TIME will allow for hour, minute, or second is 32,767. Larger values will return a #NUM! error.
• If hours, minutes, or seconds are provided as a negative number, TIME will return a #NUM! error.