Excel TIME Function – How To Use

The TIME function in Excel is categorized as a DATE/TIME function and allows you to create time values using the hours, minutes, and seconds components. It's a particularly handy function when you need to create a custom time value inside another formula.

In a business setting, you may want to use the TIME function to add a timestamp to a business report if the data set you already have has time values inserted as text strings.

Syntax

The syntax of the TIME function is as follows:

Arguments:

'hour' – The hour argument is a required argument where you need to insert a number between 0 and 32676. In cases where you input a value greater than 23, the function will divide the number by 24 and use the remainder of the division as the value for the hour argument.
'minute' – The minute argument is a required argument where you need to insert a number between 0 and 32676. In cases where you input a value greater than 59, the function will automatically convert it to hours and minutes.
'second' – The second argument is a required argument where you need to insert a number between 0 and 32676. In cases where you input a value greater than 59, the function will automatically convert it to hours, minutes, and seconds.

Important Characteristics of the TIME function

• The TIME function is available in all MS Excel versions starting with Excel 2000.
• The TIME function's output resets to 0 each time the hour value reaches 24.
• If you input a negative value or a value greater than 32676 in any of the three arguments, the function will return a #NUM! error.
• If you input a non-numeric value in any of the three arguments, the function will return a #VALUE! error.

Examples

Let's try to see some examples of the TIME function.

Example 1 – Plain Vanilla Formula for the TIME Function

The plain vanilla version of the formula will help you get a bird's eye view of the most basic use-case of the TIME function. We won't nest the formula or make any complex computations. Let's just compile the three-time components to create a valid Excel time value with the following formula:

All that this formula does is take the three components and create a valid, Excel-recognized time value that you can manipulate for computation. However, notice how outputs for the same formula are different.

This is because those cells have been formatted differently. By default, your output might look like the one in cell A2. You can custom format the cell to display the seconds with a right-click > Format Cells. You'll see the Format Cells window pop up. Set the cell's Type field to h:mm:ss.

The cells have been formatted as h:mm:ss for all illustrations that follow. With that out of the way, let's take things up a notch.

Example 2 – Using Cell References and Impact of Negative Values in TIME Function

In most practical cases, you'll need to use cell references instead of manually inputting numeric values into the formula. Let's see how that works out on a worksheet. We'll also look at what the function's output looks like if you have a negative value in your dataset. Here's the formula we'll use:

Using cell references makes it easier for you to work large data sets because you can drag the border selection across and apply the formula to the entire column or row.

Notice how the output for the last row shows a !NUM error. Whenever you input a negative value or a value greater than 32676, you'll get a !NUM error.

We're done with the basics; in the next example, we shift to gear 5.

Argument Values Greater Than 23 and 59 in the TIME Function

Values greater than 23/59 are divided by 24/60. However, what's important to understand is how the remainder of this division manifests in the final output. Let's use the same formula we used in the previous example but for a different set of values:

The hours inserted in the first row are greater than 23, and the minutes and seconds inserted in the subsequent rows are greater than 59.

When the hour value is greater than 23, the entered value is divided by 24, and the remainder of this division is used as the hour value in the function. In our case, when the function divides 28 by 24, it's left with 1.167.

So, why does our final output show hours as 4?

Let's interpret what 1.167 means here. In essence, it means 1.167 days. Think about it—there are 24 hours in a day. When you say 28 hours, that's 1 day plus 4 hours, correct?

Saying 1.167 days is the same as saying 1 day and 4 hours because 0.167 translates to 4 hours (0.167 days x 24 hours/day).

The same logic applies to minutes and seconds. For instance, in row 3 we've entered the minute value as 85. Dividing 85 by 60 comes to 1.4167. Of course, it's 1.4167 hours. The impact of the minute value also extrapolates into the hour value though. Notice how even though the hour value supplied is 6, the output shows it as 7. That's because we've added another hour by entering a minute value greater than 60.

The minute component in the final output is 25 because that's just the remainder (0.4167 hours x 60 minutes/hour). You could also compute this is 85-60 = 25 minutes, but be sure to always subtract the closest lower multiple of 60 if you choose to go this route.

For instance, if we enter 125 as the minute value like in row 4, you'd have to calculate the minutes component in the final output as 125-120 (which is the closest lower multiple of 60 to 125). You'd also need to add 2 to the hours component instead of 1, since 120 minutes equals two hours.

It works pretty much the same way for seconds as well. In row 5, the 10 seconds over and above the 60 seconds are entered as the second value by the function, while a minute is added to the minute value in the final output displaying it as 37, instead of 36 as entered in the cell B5.

Recommended Reading: Excel HOUR Function, Excel MINUTE Function & Excel SECOND Function

Example 3 – Basic Arithmetic with the Time Function

Now that you fully understand how the TIME function's output works, let's talk about how you can use the output to manipulate time values on your Excel worksheet. Say you have a list of timestamps, but you'd like to add a couple of hours/minutes/seconds to those time stamps.

You can use the basic arithmetic operations inside the TIME function, like so:

Notice how this simply adds the output of the TIME function to the timestamp in A2. If the output in row 2 baffles you, refer to the previous example where we discussed how the TIME function calculates values when the minute value supplied is greater than 59. Briefly speaking, the TIME function adds 1 hour 40 minutes (i.e., 100 minutes) to the time stamp. Adding 1 hour comes to 16:35:50. Adding another 40 minutes brings the output to 17:15:50.

Alright, so we've touched base and you're now ready to create time values on Excel regardless of how big your data set is. Once you've championed these formulas, we'll have another interesting Excel function ready for you to immerse in—stay curious.