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:
where the hour, minute and second arguments are integer values representing the hour, minute and second parts of the required time.
If the resulting time is negative (e.g. if the supplied hour is < 0), the Time function returns the #NUM! error.
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.
Column E of the following spreadsheet shows three examples of the Excel Time function:
|
Formulas:
|
Results:
|
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 value 73 seconds into the time 1 minute and 13 seconds.
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: |
||||||||||||||||||||||||
|
||||||||||||||||||||||||||
| 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: |
||||||||||||||||||||||||
|
||||||||||||||||||||||||||
| 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: |
||||||||||||||||||||||||
|
||||||||||||||||||||||||||
For further details and examples of the Excel Time function, see the Microsoft Office website.
If you get an error from the Excel Time Function, this is likely to be one of the following:
| #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. |