Join Dennis Taylor for an in-depth discussion in this video Rounding time calculations to convenient intervals, part of Excel 2010: Working with Dates and Times.
Sometimes when you're calculating time differences you want the results rounded, perhaps to the nearest day, the nearest hour, and as we see in the headings here, the nearest 15 minutes or any interval you might want. At first these might seem a little tricky, but they're actually pretty easy here. A simple difference in the two entries in Columns A and B, =B2-A2. By the way, this has been pre-formatted. The format here must be that format that includes the h within a bracket.
That does allow us to account for hours over 24. That was done ahead of time. So, we see the time difference here. It's 66 hours in the first entry, and I'll double-click to copy these formulas down the column. We see the entries for others as well. With differences like this, I don't think you'd really want to round them to the nearest day, but you certainly can. If these involve hundreds of hours, sometimes maybe that would make sense. So, how do we round this to the nearest day? Equal. Now you may or may not be familiar with the function called MROUND, but it's a great rounding tool and it does allow us to round numbers to multiples that are not necessarily decimally based, in other words, powers of 10.
So, in this case, we want to take this result here, comma, and round it to the nearest one day. Now, that's closest to 3 days, which will be 72 hours. This next one is closer to five days. That's 120 hours. Instead of 96, it should be 4. So, copy these down the column. There we go. So, these are to the nearest day and there is the function. We might want to round these to the nearest hour. So, let's try this. The MROUND function again, breaking off this same difference, comma.
Now we used 1 for a day. What are we going to use for an hour? Well, an hour is 1/24th of a day, so why not 1/24? There we go, and you see how it's to the nearest hour? Again, double-clicking to copy these down the column. 110 hours and 29 minutes, well, it's closer to 110 than it is to 111. Of course, that plays out the way we expect rounding to work, and you see the results here. But that's probably not the best way to do the formula, because others will look at and say, "What is going on there?" And we probably make more sense to include this within double quotes and use "1:00".
Get the same answer and it's much more understandable and readable as someone else might be looking at this. You might not know exactly what's going on, but that does explain the situation here and it's easier to read them, what we just saw. Similarly, over here, =MROUND, same idea. We're working at a difference here, and here too. We can do the math and come up with 196, but a better way to do this, "0:15", to the nearest 15 minutes.
So, you can imagine this answer here coming up as what? 66 hours and 15 minutes. There we go. It perhaps isn't that important to everybody, but if you want the rounding to go up or down, in other words to not follow standard rounding rules, we can use a function called CEILING. This will go up to the next 15. Now, on the first example, that's going to stay the same, second example, the same. But in the third example, that's going to go to 116 hours and 15 minutes, even though it's closer to 116 hours.
So, this time, we'll use CEILING. Change all those at once. So you see what's happening here. Not to the nearest 15 minutes. We go up to the next 15-minute interval. So, even though this entry here is closer to 116 hours, we go up to 116 hours and 15 minutes because we're using the CEILING function, and that's what's being used in all of these entries here. Based on that information, I bet you can guess what the function we use is to move down to the next 15.
That's going to be FLOOR. So, in the first two examples here, we're going to get different answers than we saw before. It's going to take us down to 66 hours exactly and 110 hours and 15 minutes and we'll see what's happening there. So, if you need to round time-lapse differences, these functions as used here, the MROUND function, are ideal for handling those kinds of situations.
- Understanding how Excel records and stores dates and times
- Looking at standard date/time entry options and acceptable alternatives
- Using the TODAY and NOW functions
- Customizing date formats
- Exploring keyboard shortcuts
- Formatting time for hours over 24
- Calculating differences across dates and times
- Rounding calculations
- Working with holidays
- Validating with dates
- Converting formatted dates to usable data