Excel TIME Function
Summary
The Excel TIME function is a built-in function that allows you to create a time with individual hour, minute, and second components. The TIME function is useful when you want to assemble a proper time inside another formula.
Purpose
Create a time with hours, minutes, and seconds
Return value
A decimal number representing a particular time in Excel.
Syntax
=TIME (hour, minute, second)
Arguments
- hour - The hour for the time you wish to create.
- minute - The minute for the time you wish to create.
- second - The second for the time you wish to create.
Usage notes
The TIME function creates a date in serial number format from the hour, minute, and second components you specify. Use it to create a valid time when you have (or can supply) these component values separately. Once you have a valid time, you can format it any way you like.
Examples
TIME formula examples
Top convert a timestamp entered as text into a proper Excel time, you can use the MID function to extract components and the TIME function to assemble the time. In the example shown, the formula in F5 is:
=TIME(MID(B5...
To generate a sequence of times, you can use the SEQUENCE function, in combination with functions like TIME, HOUR, MINUTE, and SECOND. Or you can use SEQUENCE with raw numeric values that represent time. In the example...
To count times that occur within a certain range, you can use the COUNTIFs function. In the example shown, the formula in E7 is:
=COUNTIFS(B5:B11,">="&E5,B5:B11,"<"&E6)
How this...
To add a given number of hours to a time, you can add hours divided by 24, or use the TIME function.
In the example shown, the formula in D5 is:
=B5+(C5/24)
Note: make sure results are formatted as time.
How this...
To parse a text string that represents a time into a proper Excel time, you can use a formula based on the RIGHT, LEFT, MID, and TIME functions. In the example shown, the formula in F5 is:
=TIME(LEFT(E5,2),MID(E5,3,2...
To sum the total amount of time over 30 minutes, given a set of times that represent duration, you can use the SUMPRODUCT and TIME functions. In the example shown, the formula in G5 is:
=SUMPRODUCT((times-TIME(0,30,0...
To add a given number of minutes to a time, you can add minutes divided by 1440, or use the TIME function.
In the example shown, the formula in D5 is:
=B5+(C5/1440)
Note: make sure results are formatted as time....
Related videos
Although you can use Excel's AutoFill feature to fill in a series of dates and times, you can also do the same thing with formulas. The advantage of using a formula is that can easily change the starting value and generate a new series. Let's take a look.
In this video, we'll look at functions that will let you extract the hour, minute, and second values from a time, and a function called TIME that will let you put them together again.
Related functions
