What is the TIME Function?
The TIME function is categorized under DATE/TIME functions. TIME helps us to create a time with individual hour, minute and second components.
While doing financial analysis, we would like to put a time stamp on a report. In such scenarios, TIME can be used to convert a text string into a decimal that represents time. The function can also be used to merge individual values into a single time value.
=TIME(hour, minute, second)
The TIME function uses the following arguments:
- Hour (required argument) – It can be from 0 (zero) to 32767 representing the hour. If a value is more than 23 then it shall be divided by 24 and excel shall treat the remainder as the hour value.
- Minute (required argument) – It can be from 0 (zero) to 32767 representing the minutes. If a value is more than 59 then it shall be converted to hours and minutes.
- Second (required argument) – It can be from 0 (zero) to 32767 representing the seconds. If a value is more than 59 then it shall be converted to hours, minutes and seconds.
The decimal number that is returned by TIME function is a value between 0 (zero) to 0.99988426, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).
How to use the TIME Function in Excel?
As a worksheet function, TIME can be entered as part of a formula in a cell of a worksheet.
To understand the uses of the TIME function, let’s consider few examples:
Suppose we extracted data from a PDF file and got the following data:
Now, if we wish to put the time in the cell itself then we use the TIME function. The formula to be used would be =TIME(B5,C5,D5) that TIME(hour, minutes, seconds).
We get the results below:
If the end result is a negative number, TIME will return a #NUM! error. In row 4, the hours are 24 so it is greater than 23. Any value greater than 23 will be divided by 24 and the remainder will be treated as the hour value. So, 24 would be 00 and we got -1 as seconds, which is negative, so it got subtracted to give 11:59. Remember, if the supplied second argument is negative or is greater than 59, the time extends back or forward into the previous or following minute.
If the given minute argument is negative or is greater than 59, the time extends back or forward into the previous or following hour. So, in row 6, we get the result as 5:59 pm.
If we wish to add time to a given number of hours, we can add hours divided by 24, or use the TIME function. Suppose we are given the following data:
We can use the TIME function to add the number of hours to given time. The function saves us from the need to remember the formula for converting decimal hours to an Excel time. The formula to be used is =A2+TIME(B2,0,0).
We get the results below:
Remember that the TIME function will “roll over” back to zero when values exceed 24 hours, as seen in rows 5 and 6 above.
If we wish to subtract hours from given time, we need to use the formula =MOD(A2-TIME(B2,0,0),1).
The MOD function will take care of the negative numbers problem. MOD will “flip” negative values to the required positive values.
Things to remember about the TIME function
- TIME was introduced in MS Excel 2000 and is available in all later versions.
- #NUM! error – Occurs if the given hour arguments evaluate to a negative time, e.g., if the given hours are less than 0.
- #VALUE! error – Occurs when any of the given arguments is non-numeric.
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources: