Join Dennis Taylor for an in-depth discussion in this video Rounding time calculations to convenient intervals, part of Excel 2007: Working with Dates and Times.
- View Offline
If you're working with data that has starting and ending dates and times, sometimes you want to not only calculate the difference, but you want to round that difference to a convenient number. The date information in columns A and B also include times, and the variances, in many cases, extend over multiple days. So we can simply subtract the 2, equal the later time, minus the earlier time, to get an answer here, and double- click this to copy it down the column. So 66 hours, 110 hours. This one's within the same day. It's 8 hours, and so on.
We see what's working here. That might be exactly what you need. Fine. End of discussion, possibly. But you might want to round these results in different kinds of ways. So the headings up here suggest what we're about to do. Perhaps less likely in this environment, unless the numbers are huge, round this to the nearest day. In all three cases here, although there are multiple possibilities for using different functions, the one function that's going to help us in all three of these situations is the one called mround. mround, different than round, allows you to round numbers to multiples that are not necessarily powers of 10.
So what we want to do in this example here is to take that same difference, the ending date and time minus the earlier date and time, comma, and round it to the nearest day. Simply putting in the unit 1 will do this for us. So 66 hours is closest to 3 days, which would be 72 hours. 110 hours coming up. You can probably figure that out. It's going to be 4, actually 5. It's 10 hours away from being 120, and so on.
So I'll copy this down. And in this case, of course, that's 0 to the nearest day. Again, in certain situations I think you can see how this could be valuable, particularly if the numbers are quite large in terms of hours. Now, what if we wanted to round this to the nearest hour? Again, equal mround. In this case, we want to take that same difference and round these to the nearest 24th of a day, 1/24, and you can see the difference there, 66 instead of 66 hours and 10 minutes.
So once again, double-clicking to copy this down the column, we see what's happening in the other case that's here. The 29 is almost 30, but not quite. So it goes back to 110, and we see what's happening in the other cases as well, rounding to the nearest hour, and again, this is how the function works. We're rounding to the nearest 1/24 of a day. Looking ahead to round to the nearest 15 minutes. Two ways to approach this. You could say, well, that's 1/4 of 1/24, and you could do some math in your head and figure that one out.
It's going to be 1/96th. So we want to round this way. This is probably not the best way for most people, because you always have to explain what it means if someone else is looking at it, but double-clicking we'll see that it does work. The results are rounded to the nearest 15 minutes. A better way to do this for documentation purposes would be, instead of the 1/96th, double quote, and then 0:15, certainly more readable, and I think anybody looking at it would say, well, oh yeah, now I get it, and we'll change all these at once.
So again, displaying it this way, to the nearest 15 minutes, and that's what's done in all these cases here. And you can certainly adapt this and adjust it to other kinds of amounts that you might want to round time calculations to: 15 minutes, 10 minutes, 30 minutes, whatever. Good, quick, easy ways to deal with time and math.
- 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