How to round time in Excel - round to full hour, 30 minutes or 15 minutes

By: Ilkka Mäkinen | Published: 17 March, 2016 | In: Excel

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: 

  1. The time we want to round
  2. 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. 

 

Subscribe to our newsletter

Get the latest expert advice from our blog on Excel, WordPress, search engine optimization, Google analytics and Adwords straight to your email.

 

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!

By: Ilkka Mäkinen | Published: 17 March, 2016 | In: Excel

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.

Want to stop messing about and learn to use Excel efficiently?

Save your spot in our Excel Essentials course and learn how to make Excel rock!

Terms & Conditions

Conditions: Minimum number of participants is 3 persons on each course. If the course you are enrolling in does not reach the minimum number of participants it will be cancelled or postponed. We will inform enrolled participants in case there are not enough participants on a course.

Cancellations and substitutes: a) Cancellations confirmed more than 21 days prior to the date of the course are fully refundable and free of charge. Cancellations are to be made in writing to info@talentwire.eu. 

b) Cancellations made within 21 days of the date of the course may not be cancelled without incurring course charges fully.

c) Substitutes may be made at any time free of charge. TalentWire should be informed of any changes.

Pricing and invoicing: Payment for each course is to be received no later than 14 days prior to the course start date. We will send an invoice after your booking has been confirmed. Inform us of any special arrangements you require for your invoices.

All prices are as stated and are exclusive of VAT which will where appropriate be charged at the current rate.

×