Exceljet

Quick, clean, and to the point

How to work with times

Excel contains 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.

Let's take a look.

Here we have a set of random times in column B. First, I'll add a formula to column C to pick up the time values in B and format them in the general format so you can see the raw time value.


Now let's extract the components of each time using Excel's HOUR, MINUTE, and SECOND functions.

First, I'll extract the hour value using a function called HOUR. HOUR takes one argument, a time in serial number format. When I give it the value in column C, it returns just the hour from the date, in this case 12.

When I copy the formula down, you can see the hour component of each time.

To get minutes, use the MINUTE function. Like the HOUR function, MINUTE requires only a valid time.

Finally, the SECOND function will return just the seconds for a time.

All of these functions will calculate a new result when a time value changes.

Now let's recombine these date components into a time again. For that, we need to use the TIME function. TIME takes hours, minutes, and seconds as separate arguments and returns a valid time.

Since we have all of these values already exposed on the worksheet, I can easily add them to the formula and copy it down.

With all time components available, I can easily adjust the values going into the TIME function.

For example, I can add 15 minutes to the first time. The result is 12:15 PM.

If I add 60 minutes to a time, notice that Excel takes care of changing the hour as needed.

The same is true with seconds. If I add 600 seconds to the second time you can see that 10 minutes are added. If I subtract 600 seconds, 10 minutes are removed.

Be careful if you're working with a value that contains a date and time, like the value in B11. The HOUR, MINUTE. and SECOND functions correctly extract the time components the TIME function creates a valid time.

But notice that the date value is stripped off the date when we use the TIME function at the end. That's because we aren't preserving the date anywhere in our calculations.

Course 

Related shortcuts

CtrlZ
Z
Author 
Dave Bruns