How to round time in Excel - round to full hour, 30 minutes or 15 minutes
This is a tutorial on how to round time in Excel. This can feel a bit tricky if you are not familiar with how Excel handles time formatted data. Now we can't just go ahead and use the ROUND function which might feel like the obvious choice. We need a different set of tools for this, because for Excel, data formatted as time behaves in a different way.
We have three options for rounding time in Excel:
- CEILING, which rounds time up
- FLOOR, which rounds time down
- MROUND, which rounds time to the nearest time interval
For all of these functions, we need to tell two things:
- The time we want to round
- The accuracy we want to round to (Excel calls this "significance")
Let's now pretend we have a random time, say 12:43 in cell A1. I want to round that time up to nearest 30 minutes (to 13:00).
I would then go with:
CEILING(the time we want to round;the accuracy which excel calls "significance")
This would look like this:
CEILING(A1;1/24)
So why is accuracy 1/24?
Because for Excel, one hour is 1/24.
This means:
- 1 min = 1/24*60=1/1440
- 15 min = 1/1440/15=1/96
- 30 min = 1/1440/30 = 1/48
So if you want to round to nearest full hour, you use 1/24 as "significance". If you want to round to nearest 30 minutes, you use 1/48 as the "significance". 15 minutes would be 1/96.
That's it in a nutshell.
Let's try this.
You want to round time in cell A1 up to nearest 30 minutes. What do you do?
Yes, you figured right:
CEILING(A1;1/48)
Now if you need to round time down to nearest 30 min interval, you would go with:
FLOOR(A1;1/48)
And finally, ff you need to round time to nearest 30 min interval:
=MROUND(A1;1/48)
If you want to understand more on how Excel handles time, you can join our Excel Essentials training course. For those who want to go deeper into Excel's advanced features, the Advanced Excel course is for you.
That's it for this tutorial, I do hope you enjoyed it! Until next time!
Sign up to our newsletter
Expert advice on Excel, WordPress, SEO, Google analytics and Adwords. Sign up to our newsletter to get the latest posts directly into your inbox.