Purpose

Return value

Syntax

=TIME(hour,minute,second)
  • hour - Number of hours.
  • minute - Number of minutes.
  • second - Number of seconds.

Using the 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
Output from TIME formatted in different ways - 1

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.

Purpose

Return value

Syntax

=TIMEVALUE(time_text)
  • time_text - A date and/or time in a text format recognized by Excel.

Using the TIMEVALUE function

Sometimes, times in Excel appear as text values that are not recognized properly as time. The TIMEVALUE function is meant to parse a time that appears as a text value into a valid Excel time. A native Excel time is more useful than text because it is a numeric value that can be formatted as time and directly manipulated in a formula.

The TIMEVALUE function takes just one argument, called time_text . If time_text is a cell address, the value in the cell must be text. If time_text is entered directly into the formula it must be enclosed in double quotes (""). Time_text should be supplied in a text format that Excel can recognize, for example, “6:45 PM” or “18:45”. TIMEVALUE ignores dates if present in a text string.

The TIMEVALUE function creates a time in serial number format from a date and/or time in an Excel text format. TIMEVALUE will return a decimal number between 0 and 0.99988426, representing 12:00:00 AM to 11:59:59 PM. Because the maximum value returned by TIMEVALUE is less than 1, hours will reset every 24 hours (like a clock).

Examples

The formulas below show the output from TIMEVALUE:

=TIMEVALUE("12:00") // returns 0.5
=TIMEVALUE("12:00 PM") // returns 0.5
=TIMEVALUE("18:00") // returns 0.75

To display the output from TIMEVALUE as a formatted time, apply a time number format .

Alternative formula

Notice that the TIMEVALUE formula in C15 fails with a #VALUE! error, because cell B15 already contains a valid time. This is a limitation of the TIMEVALUE function. If you have a mix of valid and invalid dates, you can use the simple formula below as an alternative:

=A1+0

The math operation of adding zero will cause Excel will try to coerce the value in A1 to a number. If Excel is able parse the text into a proper time it will return a valid time as a decimal number. If the time is already a valid Excel time, adding zero will have no effect, and generate no error.

Notes

  • TIMEVALUE will return a #VALUE error if time_text does not contain time formatted as text.