The TIME function in Excel allows for custom time calculations where you can change hours, minutes and seconds.
This can be useful if you want to create a time value linked to cells, or make calculations from time values that are already in cells, like seeing how they would change if a set number of hours, minutes or seconds were added to them.
Download File
If you would like to follow along download the attachment below.
What is the TIME Function
The TIME function returns a time value based on amounts that are entered comprising the hour, minute and second.
The syntax of the TIME function is:
=TIME(hour, minute, second)
hour: The hour component, values between 0 and 23.
minute: The minute component, values between 0 and 59.
second: The second component, values between 0 and 59.
Notes on Formatting
Excel uses serial number based on decimal values between 0 and 1 to represent time values. These serial numbers can then be formatted to appear in different time formats.
For example, the decimal value 0.75 represents 6pm, as this is three quarters of the way through the day. The format of the decimal value can be changed to 18:00, 06:00 PM or 6:00 PM, but the underlying decimal value will always remain the same.
The values in the examples below have been formatted to show as time values rather than decimals, but depending how your cells are formatted you may see a decimal value when using the TIME function.
If this is the case the quickest way to get it to show a time value is to go to the Home tab and select the drop down in the Number section.
This will drop down a list of preset number formats. Select Time to change a decimal value to a 24 hour time format.
To learn more about changing time formats and why Excel uses an underlying decimal number for times click here.
Creating Time Values With the TIME Function
As each part of the TIME function makes up part of the time, entering a value in each component will generate a time value.
To have the value for 5:10 AM and 30 seconds, the TIME function could be filled out as follows:
Not all values of the time function need to be filled in. If you don’t want to have values for part of the time these can be left as zero.
If enter a value outside the typical range for each time unit, like 25 hours or 61 minutes, excel automatically adjusts the values.
Linking TIME to Cells to Dynamically Update the Time
As each part of the TIME function makes up part of the time, these can be linked to cells instead of being hardcoded directly into the function.
This allows for values in the cells to be changed to update the time value.
Formatting the TIME Function Output
When entering the hour value in the TIME function this needs to be in a 24 hour format regardless of what format you want your output to be.
In Excel there are many ways to format the output of a time value.
Two of the most common time format will be either 12 hour or 24 hours.
You can then change the format of the cell with the output.
Creating Calculations with TIME
TIME functions can be added or subtracted from each other to give total times.
When subtracting one time value from another make sure that the smaller time is being subtracted from the larger time. If trying do this the other way you will get the hash value (#) repeating itself.
Calculating from an Existing Time
As well as adding TIME functions together, you can use the function to manipulate existing time values that have either been hardcoded into the spreadsheet or generated from a function.
Starting with the value 18:00:00 hardcoded into a cell, you could see how this increases incrementally by using the TIME function linked to a cell.
This also works with negative values.
Conclusion
The TIME function allows you to create your own time values by entering specific hours, minutes or seconds.
These can also be linked to a cell to dynamically update a time value by changing values in the linked cells.
The TIME function can also be used to create time calculations, both by adding or subtracting TIME functions from each other, or by creating a formula with an existing time value that has been hardcoded into a cell.
Comments