The Excel TIME Function

Related Function:
DATE function

Basic Description

The Excel Time function accepts three integer arguments representing hours, minutes and seconds, and returns an Excel time.

I.e. the function returns the decimal value that represents the time in Excel.

The syntax of the Time function is:

TIME( hour, minute, second )

where the hour, minute and second arguments are integer values representing the hour, minute and second parts of a time.

Like all Excel times, the time returned from the Excel Time function is stored, within Excel, as a decimal (see the page on Excel Dates & Times for further information on this). Therefore, if you want the result of the function to be displayed as a time, you will need to make sure that the cell containing the function is formatted with an Excel 'Time' format.


Time Function Arguments

Typically, when using the Time function, the supplied hour argument will be between 0 and 23 and the supplied minute and second arguments will both be between 0 and 59. However, these values can extend outside these ranges, in which case, they behave as follows:

hour-

If the supplied hour is greater than 23, this value is divided by 24 and the remainder is used as the hour part of the returned time. For example:

TIME( 24, 0, 0 )=00:00:00
TIME( 25, 0, 0 )=01:00:00
.
.
.
.
.
.
minute-

If the supplied minute argument is negative or is greater than 59, the time extends back or forward, into the previous or following hour. For example:

TIME( 1, -2, 0 )=00:58:00
TIME( 1, -1, 0 )=00:59:00
TIME( 1, 0, 0 )=01:00:00
TIME( 1, 1, 0 )=01:01:00
.
.
.
.
.
.
TIME( 1, 59, 0 )=01:59:00
TIME( 1, 60, 0 )=02:00:00
TIME( 1, 61, 0 )=02:01:00
second-

If the supplied second argument is negative or is greater than 59, the time extends back or forward, into the previous or following minute. For example:

TIME( 1, 1, -2 )=01:00:58
TIME( 1, 1, -1 )=01:00:59
TIME( 1, 1, 0 )=01:01:00
TIME( 1, 1, 1 )=01:01:01
.
.
.
.
.
.
TIME( 1, 1, 59 )=01:01:59
TIME( 1, 1, 60 )=01:02:00
TIME( 1, 1, 61 )=02:02:01

Note also that:

  • If any of the Time function arguments are supplied as decimals, these are truncated to integers;
  • If the resulting time is negative (e.g. if the supplied hour is < 0), the Time function returns the #NUM! error.

Time Function Examples

Column E of the following spreadsheet shows three examples of the Excel Time function:

 Formulas:
 ABCDE
1HrsMinsSecs Time
2    =TIME( 5, 44, 32 )
352155 =TIME( A4, B4, C4 )
40073 =TIME( A5, B5, C5 )
 Results:
 ABCDE
1HrsMinsSecs Time
2    05:44:32
352155 05:21:55
40073 00:01:13

Note that the results in column E of the above spreadsheet are formatted with the Time format hh:mm:ss.

Also note that, in cell E4 of the example spreadsheet, the Excel Time function successfully converts the supplied value, 73 seconds, into the time 1 minute and 13 seconds.


Further details and examples of the Excel Time function are provided on the Microsoft Office website.


Time Function Errors

If you get an error from the Excel Time Function, this is likely to be one of the following:

Common Errors
#NUM!-Occurs if the arguments evaluate to a negative time (e.g. if the supplied hour is < 0).
#VALUE!-Occurs if any of the supplied arguments are non-numeric values.