TIME Function
Create a time with individual hour, minute and second components
Create a time with individual hour, minute and second components
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:
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.).
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.
Click here to download the sample Excel file
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:
To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user. Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.
Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!