Calculating with hours, minutes, and times of day

- Excel gives us tools, not only for calculating differences between dates, but also between times. Typically, if we're measuring time differences, we've got a starting time, an ending time, and a simple formula here subtracting the two, and we always want to be subtracting the later time minus the earlier time, = the later time - the earlier time, we get an answer. Now, as is often the case when you work with times, you get funny results based on the format.

The actual answer here is 5:49, but the AM throws us a bit. Very often, when you're working with times, you'll need to go into Format Cells, and you can get there quickly with Control + 1. Time category, most of the time when you're making changes, you'll either change to 13:30 or the 1:30 PM. This adds the AM/PM indicator. We don't want that here. 13:30 allows us to display times up to 24 hours, so we see that.

Double click, we see the calculations on the others. If, somehow, we've got these backwards, or, let's say, for example, I'm going to change this one, I'll make it be PM. The formula result here is all pound signs. If you've seen these before, and one of the things probably a lot of you do is you make the column wider, either by double clicking or dragging it, but no matter how wide you make this, it's impossible, because we're trying to take a later time and the earlier time here in the wrong order.

So I'll just press Control + Z a few times. Back to here, and let's-- Now we have the AM/PM in the correct order here. It's not always because they're AM and PM, for example, here are two AMs, we can certainly do it that way. This cluster of cells right here, same situation, except the times are being displayed in 24 hour style. And below that, a bit unusual, but unorthodox, really, we see the times mixed up in different display techniques, but the math will work the same in all cases. In fact, I can take these calculations right here, and, holding down the Control key and dragging the bottom edge, simply copy them to here, and then also to here.

There could be times when you want to display 24 hour type style numbers like these here, display them with AM/PM. There is a key shortcut for that. It's Control + Shift + @, that's the same as Control + Shift + 2. We have that display. There is no corresponding key stroke for converting them to display in 24 hour styles. We can always, by way of Control + 1, consider the change in the format. Now, we can also subtract dates and times over a multiday period.

The problem here, at least at first, is a little unusual, because we'll do our subtraction like before, = the later time - the earlier time, we will get an answer, but, at first, it doesn't seem to make sense. Now, if you look at that more closely, you realize it really is 2.375 or two and three eighths days but who wants it displayed that way? So let's change it. Now, we could use Control + Shift + @, but that's not really going to help.

That puts in the AM/PM, it also gives us an answer that doesn't seem to make a lot of sense. So let's press Control + 1. First thought would be here, Time in the Category group here, 13:30, and now we're getting 9:00. If you were to analyze this a bit, you'd realize that's how many hours it's over the two day time frame here. We're actually looking at a 57 hour difference. Now, I wish I had a good explanation as to why it displays this, but there is an adjustment, but you wouldn't know it at first.

If you're dealing with times that go over 24 hours, you'll get an answer like this if you use the standard display. Control + 1 takes us back to Format Cells. Choose the option that has a 37, as if we should all know that. If you do click that, by the way, you will see up above on the Sample the correct answer. One other oddity here, it displays hours, minutes, and seconds. Say we don't want those here. Immediately jump over to Custom, and then, in the format up here, take off everything to the right of the mm, so hours, minutes.

The h within brackets means it can handle times over 24 hours, and you wouldn't instinctively know that, but that's the fact, click OK, and now we have a sensible answer. So be on the lookout for those kinds of things. When you're making these kinds of entries, by the way, I'm going to type "4/2/15 21:30". That's how we do it. And I will type on the second example something a little bit different, "4/5/15 " Now, what if it were 6:00 PM exactly? "6 p" and we can enter it that way, too.

So different techniques for entering these. The difference here, and I'll simply drag that down, is 68 hours and 30 minutes. So we see a difference there, too. Now, in the next worksheet over, RoundingTime, we're going to calculate differences here, this is similar to what we just saw. I'm going to do them all at once by highlighting these. In this example, =B2-A2, and I'll press Control + Enter since all those cells are highlighted, and here we get an answer, and this might throw us at first, but again, from our previous example, we know what to do here.

Let's press Control + 1, Time, not 13:30 but 37:30, jump over the Custom, and keep only the h within brackets and the :mm and we have sensible answers there. Now, maybe we want to round these to the nearest fifteen minutes. When you're rounding, and we're not talking about decimal values, we use a different set of rounding functions. In other words, we're not using ROUND or ROUNDUP or ROUNDDOWN, we use MROUND, this data right here, comma.

Now, what I'm about to put in here might surprise you. "0:15", we want to round this to the nearest 15 minutes, so what we would be expecting to see here, and we hope to see, is 66:15, because this is closer to that than it is to 66:00. Here's our answer, and just like before, we want a different format here. How about this format? So I'm going to drag this format rightwards by selecting the cell, drag any edge with the right mouse button, drag it over here with the right mouse button, Copy Here as Formats Only, that's what we get.

So, here we expect to see the same number, down here it's going to be 154, actually it'll go to 154:45, and so on, to the nearest 15 minutes, and there we see it, in all cases we're using MROUND. If we always wanted to go up to the next 15 minute level, it wouldn't be MROUNDUP, there is no such function, but it would be CEILING, it would automatically go upward, no matter what. If we wanted it to go down to the previous 15, we use FLOOR, so I'll use that here, and we see the entry.

So it's going downward to the next 15 minute break. That's using FLOOR. Now, once we've done this for 15 minutes, you can imagine how we might do this for hour, or even for day. So I'm going to drag this rightward, then make an adjustment, we want to be referring to cell C2, and if we want it to go nearest, we'll use MROUND again. If we want it to always go downward, we'd use FLOOR. Now, instead of 15, though, we want this to be one hour, so it's going to be "1:00", like this.

By the way, another way to do this, not necessarily better, is to put in one twenty-fourth, so we could put in here 1/24. That works as well. So there's one example, 1/24, or, within double quotes, 1:00. This is probably clearer to those who are looking at this and wouldn't exactly know what's going on, this is probably a little clearer than using one twenty-fourth. And by the way, for the example here, if we wanted to use a fraction, we'd use one ninety-sixth, that's a fourth of one twenty-fourth for 15 minutes.

And, of course, to the nearest day, we could drag this rightward and make an adjustment here, too, and what would be do here? Well, first of all, if we're using one twenty-fourth, let's go back to our example here where we're using the 15 since that's the better idea. Drag this rightward, again, make our adjustment, instead of D2, it's going to be C2. and put in 24:00, you can do it this way. And that answer, although it is correct, takes us to the nearest day this way, we might want to display this just as a value, in other words, the value 3, so we can make this be general format.

There is a keystroke shortcut for that, Control + Shift + ~, but if you didn't know that, here, too, right click, go to Format Cells, or press Control + 1, either way, choose General, click OK, and that's a sensible answer. This rounds to the nearest number of days. So working with these is tricky at times, it's not always clear when you want to be changing formats, but usually the context will dictate that. Now, on the next sheet over, when we're working with times and rates together, in other words, dollars and time, sometimes we get funny answers.

You know what the answer should be here. If someone has worked 10 hours and charges $48 an hour, we know this answer should be $480. Look what happens when we multiply these. $20, we know that isn't correct. What we need to do in circumstances like this is multiply by 24, talking about 24 hours in a day, of course. And it's not always clear which number we'll be using here, but when you're working with dollars and times together, you will need, often, to multiply these by 24, and sometimes when you're going the opposite direction, you'll need to be dividing by 24.

Now, we can actually copy these off to the right because, if we want to do this by minutes, it's going to be very similar, but make a change. If we copy these off to the right, we don't have correct answers. $2 a minute, 10 minutes, that should be $20. So what do we have here? And what do we need to do here? Since we're doing this by the minute, multiply these by 60. Make the change, and then copy this down the column. So, different thoughts, different ideas for working with times. And we saw in the previous worksheets the techniques that were needed for dealing with time differences and rounding with times.