How to use the TIME function in Microsoft Excel

Let's look at some Excel Time function examples and explore how to use the Time function as a worksheet function in Microsoft Excel:

Summary

The Excel TIME function is a built-in function that allows you to create a time with individual hour, minute, and second components. TIME Function in Excel is helpful in assembling the proper time inside any other formula.
The Time function is a built-in function in Excel that is categorized as a Date/Time Function.

Purpose

Create a time with hours, minutes, and seconds.

Return value

A decimal number representing a particular time in Excel.

What Does It Do ?

This function will convert three separate numbers to an actual time.

Syntax

=TIME(hour, minute, second)

Parameters or Arguments

Hour - The hour for the time you wish to create. (This can be any number between 0 and 32767)
Minute - The minute for the time you wish to create. (This can be any number between 0 and 32767)
Second - The second for the time you wish to create. (This can be any number between 0 and 32767)

Formatting

The result will be shown as a time which can be formatted either as 12 or 24 hour style.
If a normal number format is applied a decimal fraction is shown which represents the time as a fraction of the day. By using the Format,Cells,Number,Time command the format can be changed.

Usage notes

The Time function creates a valid Excel time using individual hour, minute, and second components. For example, you can use the TIME function to create the times 10:15:00.

=TIME(10,15,00) || Result: 10:15:00 AM

The TIME function is useful for assembling times that need to change dynamically based on other values. For example, 10 hour in cell B5; 00 minute in cell C5 and 00 in cell D5:

=TIME(B5,C5,D5) || Result: 10:00:00 AM

If B5 is then changed to 04, the TIME function will return a time for 04:00:00 AM or
If C5 is then changed to 15, the TIME function will return a time for 10:15:00 AM or
If D5 is then changed to 45, the TIME function will return a time for 10:00:45 AM

Note

• 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.
• The decimal number returned by TIME is a value ranging from 0 (zero) to 0.999988425925926, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).
• Time values are a sub-part of a date value and are represented by a decimal number (for example, 12:00 PM is represented as 0.5 because it is half of a day).
• If your cell is formatted as General and you enter the TIME function, Excel will format your result as HH:MM AM based on your Regional Settings. If you wish to see the serial number result from the TIME function, you will have to change the format of the cell to General after entering the formula.
• #VALUE! Error – Occurs if any of the given argument is non-numeric.
• Hour – If the hour value is larger than 23, it will be then divided by 24. The remainder of the division will be used as the hour value. For better understanding, TIME(24,0,0) will be equal to TIME(0,0,0), TIME (25,0,0) means TIME(1,0,0) and TIME(26,0,0) will be equal to TIME(2,0,0) and so on. If the hour is a negative number, TIME will return a #NUM! error.
• Minute – If the minute value is larger than 59, then every 60 minutes will add up 1 hour in the preexisting hour value. For better understanding, TIME(0,60,0) will be equal to TIME(1,0,0) and TIME(0,120,0) will be equal to TIME(2,0,0) and so on.
• Second – If the second value exceeds 59, then every 60 seconds will be adding 1 minute to the preexisting minute value. For better understanding, TIME(0,0,60) will be equal to TIME(0,1,0) and TIME(0,0,120) will be equal to TIME(0,2,0) and so on.

Excel Time Function Examples

The following spreadsheet shows some simple examples of the Excel Time function:

Because the Time function returns a serial time, we wanted to show you the result from the TIME function as an unformatted serial time which you can see this in column E.

We also wanted to show you the result as a formatted time which is how a user would format the results. In column F, we have formatted the result from the TIME function as hh:mm:ss AM/PM and hh:mm:ss.

The above examples show that each argument to the Time function can be supplied as a single value or cell reference, or as an array of values or cells.

Click here to learn more excel function