Exceljet

Quick, clean, and to the point

Excel TIME Function

Excel TIME function
Summary 

The Excel TIME function is a built-in function that allows you to create a time with individual hour, minute, and second components. The TIME function is useful when you want to assemble a proper time inside another formula.

Purpose 
Create a time with hours, minutes, and seconds
Return value 
A decimal number representing a particular time in Excel.
Syntax 
=TIME (hour, minute, second)
Arguments 
  • hour - The hour for the time you wish to create.
  • minute - The minute for the time you wish to create.
  • second - The second for the time you wish to create.
Version 
Usage notes 

The TIME function creates a valid Excel time based with supplied 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

Examples

=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

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

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

However, when total time reaches 24 hours, the TIME function will "reset" to zero.

=TIME(12,0,0) // 12 hours
=TIME(36) // 12 hours

In this way, TIME behaves like a 24 hour clock that resets when it crosses midnight. Notably, TIME will not handle numeric inputs larger 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! 

As a workaround, you can convert hours, minutes, and seconds directly to Excel time with a formula:

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

The result is the same as the TIME function up to 24 hours. Over 24 hours, this formula will continue to accumulate time, unlike the TIME function.

Notes

  • When 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.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.