How to work with times (video)

Abstract

In this video we'll look at functions that will let you extract the hour, minute, and second values from a time, as well as a function called TIME that will let you put them together again.

Transcript

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 back 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 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.

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

When I copy the formula down, you can see the hour component for 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 values available, it's easy for me to 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 a time, you can see that 10 minutes get added. If I subtract 600 seconds, 10 minutes are removed.

Be careful if you're working with a value that contains both a date and a time, like the value in B10. The HOUR, MINUTE, and SECOND functions correctly extract all of the time components, but notice that the date value is stripped off the date when we use the TIME function.

That's because we aren't preserving the date anywhere in our calculations.