Round To Quarter Hour

Learn Excel From MrExcel Podcast, Episode 2210: Round Up to the Next Quarter Hour.

Hey, welcome back to MrExcel netcast, I'm Bill Jelen. Today, another question from my Atlanta Power Excel seminar. Someone was doing a billing spreadsheet, and they wanted to round up to the next quarter hour, right? So, for each task here, they have a Client, Start Time, and End Time, and to calculate the Elapsed Time-- that's simple, if those are real times, or if they're just =E2-D2. So, that's saying 55 minutes, but it's saying it in a really weird way-- "12:55 AM." We want to go ahead and format this. Choose Time, and hey, look, there's a few different choices here: 13:30; 13:30:55, which would show the seconds; and then 37:30:55. Now, for right now, I'm going to choose the 13:30, but we're going to circle back later on and re-examine that choice. So, I choose OK and double-click to copy it down, and you see that it is just doing the math -- so here, 25 minutes. But, the person who asked this question-- the firm they work for-- they want to round up to the next quarter hour.

Alright, so, quarter hour. Let's think about times in Excel. Noon is 0.5. If I format that as a time-- 12:00:00 PM. And if I do 0.25, that'll be 6:00:00 AM. Alright, so the way this works is that one full day is the number 1.0, and so, if we wanted to figure out 15 minutes, it would be-- there's 24 hours x 4 quarter hours, so we'd have to put =1/96 in there, and that gives us 15 minutes. Now, 1/96 -- I can kind of do that math in my head: 24x4; 1/96 -- it's certainly easier to remember 1/96 than to remember this decimal: 0.01041666667. Alright, so it was easier to use 1/96 there, and so we're going to use a function called CEILING.

CEILING. See, we have a choice between CEILING and CEILING.MATH for positive numbers that are identical. It's shorter to type CEILING, so I'm going to use CEILING.  And, rounded, there's 1/96 of a day-- which is 15 minutes-- and that's the right answer, in the wrong format. We're going to copy these formats over-- Ctrl+C, and then Alt+E+S+T to paste special formats-- and the 55 minutes rounds up to the next hour. Double-click to copy that down, and, see, the 35 minutes rounds up to the next 45; 25 rounds up to the next 30. It seems to be working, although the person who asked this question-- they're rounding up to the next 15. Maybe even your company uses something different. 

Alright, so to round to the nearest quarter hour, we use 1/96. But to round to the nearest 5 minutes-- there's 24 hours, there's 12 5-minute periods in each hour, so 12x4=288. Use 1/288 to round to the nearest 5 minutes. If you round to the nearest 6 minutes-- there's 10 6-minute periods x 24 hours; you round to the nearest 1/240.  To round to the nearest minute, pick to the nearest 1/1440. 

Alright, so you can do this, and we also have a choice. CEILING is always going to round up, so 16 minutes-- let's go to 1/96 there-- 16 minutes is going to round up to 30, and, while I'm sure my attorney thinks that's a great way to bill, it's not really fair to me-- the client. Alright, so you know CEILING will round up. My attorney's never going to use this: This FLOOR will always round down. But, probably what is the fairest thing here is to use MROUND. So, =MROUND of the Time to the nearest 1/96, and that'll round down for everything up to about 22.5 minutes; and then, after that, it'll round up. Alright, so it rounds to whatever it's closest to. So, you have to choose whether you're going to use FLOOR, MROUND, or CEILING, and then what your divisor is going to be-- whether it's going to be to the nearest 5 minutes, the nearest minute, or, you know, whatever. Alright, so there we're rounding up. 

Now, a bonus tip here-- this was Larry in that seminar-- Larry says, "Hey, look, it's not really good to show billable time in times like that; we really want to convert to hours because you're really going to be billing at some rate-- you know, $35/hour, $100/hour, $500/hour, whatever your rate is-- and we need to be able to multiply the hours x the rates. So, it would be nice to be able to convert those hours to a decimal time. So, we take that time in G2 and multiply it by 24, and that's going to give us a decimal number of hours, but it's going to be in the wrong format. They're going to choose a time format in this case, when it shouldn't be a time format at all; it should just be in Number format. And make sure to include at least 2 decimal places. Double-click to copy that down, and we get our times."

Alright, now, going back to our question here of "Which Number format should we use?", this is a really good place to talk about "Why?". If we choose all these numbers, here, we'll see that the sum is 53 hours, and if I come down here and put in a SUM function, we get 53 hours. That's perfect. But, if I'm using the SUM function below a column of time, and I do Alt+= or the AUTOSUM, it gives me 5 hours...5 hours. Which is right-- 53 or 5?

Well, it turns out in some weird sense, they're both right. But, here's why they're both right. I'm going to enter a function called =NOW. You'll see I'm actually recording this back on May 9th, so, 20 days ago, and it's 7:08 a.m. And with =NOW, sometimes I want to show the date and the time, but other times, I just want to show the date. So, I can make =NOW give me the current date, or, I can make =NOW give me the current time. And so, the Number functions are happy to either truncate the time off, or remove the date off. And this one that we chose down here, the 13:30, is saying, "Hey, I don't want to see the Number of days; just show me the time." Yes, that's the problem-- because this is really 2 days and 5 hours-- 2 days is 48 hours-- 2 days and 5 hours. And we told it-- we said to Excel, "Hey, don't bother to show me the days." Well, we didn't know we were telling Excel that, but that's what we were telling Excel when we chose this one or this one. Alright, so you're forced to come down here to 37:30:55. That's the one that's going to show us the full number of hours, and we get the correct answer of 53 hours. 

But, it seems really silly to show those extra seconds when we're rounding to 15 minutes. So, let's go back into that. After you choose 37:30:55 to learn the Number format behind this, click on Custom. You'll see that the thing that makes it work is the square brackets around the hour. But, we don't need the :ss. So, now we can edit that custom format, and so we have hours and minutes. We don't need to understand today what the ;@ means. It's saying that, for text, just show the text; but, just leave that part there, even if you don't understand it. Click OK, and we now have our time in hours and minutes, leaving out the seconds. But, the totals work. Really, really subtle, that 13:30 and that 13:30:55. You know, it doesn't say, "Hey, you're telling Excel to ignore the date...the full days." But, you are.

Things like Taking Time Math are in this book: MrExcel LIVe: The 54 Greatest Excel Tips Of All Time. Click that "I" in the top right-hand corner to check it out. 

This sounded like such an easy question; I can't believe that we're 7 minutes in already. But, a company wants to bill in quarter hours, and they always want to round up to the next quarter hour. So, 24 hours in a day, 24x4=96; 15 minutes = 1/96. So, use CEILING(A2,1/96) to round up to the next higher quarter hour. Use MROUND(A2,1/96) to round to the nearest quarter hour, and then use FLOOR(A2,1/96) to round to the lower quarter hour-- always goes back. There's 24 hours in a day, so you can do 1/288 to get to 5-minute periods, 1/240 to get to 6-minute periods, 1/1440 to round to the nearest minute. And then, the bonus tip there at the end (which Number format)-- you always want to go back to that 37:30:55 if you're going to show hours in excess of 24 hours.

To download the workbook from today's video, visit that URL down there in the Youtube description.

I want to thank you for stopping by; we'll see you next time for another netcast from MrExcel.

Hey! Thanks for hanging along. But, just one more bonus tip here (that I'm not even going to bother to mention). What I started out with here-- let's clear this whole thing-- a little formula that came over and pointed to one of these decimals-- so, the nearest quarter hour. But, I wanted to show that not as the 0.010417. It's awesome-- in the Number Formatting, there's actually something called Fractions, and if I choose "Up to two digits," I'm going to get 1/96; but, I need to handle that 1/288, so I choose "Up to three digits," and it still shows me 1/96; but, then when I convert over to this one -- the 1/288 -- it will show it correctly. Now, I guess I should have done "four digits" to handle the 1/1440. What's that going to do? That's going to give me... But, if I go into the Number Format, and go to Custom, and just add an extra question mark there, then we'll get to the nearest 1/1440 of a fraction. Number Format-- cool, cool thing.