Search

# Rounding time to nearest minute or quarter hour etc. [formulas]

Share

The other day, I was building a spreadsheet to calculate FTE (full time equivalent) for staff based on hours worked on various days in a fortnight. While building the spreadsheet, I came across an interesting problem. Rounding Time to nearest minute.  We can’t use ROUND() or MROUND() to round time as these formulas aren’t designed to work with time values. Although time values are technically decimal, rounding time to nearest minute (or quarter hour etc.) can be tricky when usual round formulas. Let me share a few formulas to round time to nearest point.

Let’s say you have a time value (either user input or calculated) in cell A1.

### Use below formulas to round time in A1.

Nearest second: =TIME(HOUR(A1), MINUTE(A1), SECOND(A1)).

• SECOND formula rounds up any fractions and returns full seconds.

Nearest 15 seconds: =TIME(HOUR(A1), MINUTE(A1), MROUND(SECOND(A1),15))

• Use MROUND() to round up seconds values to nearest multiple of 15 (or whatever else)

Nearest Minute: =TIME(HOUR(A1), MINUTE(A1)+(SECOND(A1)>30),0)

• The seconds value will always be zero. We just look at fractional minutes portion to see if they are more then 30 to round up to next minute. The trick is to add up Boolean check (SECOND(A1)>30) to minutes value.

Nearest 15 minutes: =TIME(HOUR(A1), MROUND(MINUTE(A1)+SECOND(A1)/60,15),0)

• This one uses MROUND to round total mins (including fraction) to nearest multiple of 15.

Nearest 37th minute: =TIME(HOUR(A1), MROUND(MINUTE(A1)+SECOND(A1)/60,37),0)

• Same logic. Just to show you how to round to an arbitrary minute.

Nearest hour: =TIME(HOUR(A1) +((MINUTE(A1)+SECOND(A1)/60)>30),0,0)

• Check if total minutes is greater than 30 and add the result to hours.

### Time for some home work

Let’s test your timing skills. Assuming A1 has date & time value (like 26-Jun-2017 7:21:32 AM), round it up to nearest working hour.

• The working hours are 9AM to 6PM on weekdays (Monday – Friday)

### Time to polish your skills

Always having a hard time working with times in Excel? Its high time you took some time to learn about Excel time.

### Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

### Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

### Announcing Power BI Dashboard Contest (win \$500 prizes!)

Hey there, I have a SUPER exciting announcement! April is about to get a whole lot sweeter with our Power BI Dashboard Contest! Your mission, should you choose to accept it: Craft the most EPIC dashboard for the Awesome Chocolates CEO with sales & financial insights! Winners stand a chance to score up to \$500 in Amazon Gift Cards, plus some serious bragging rights!

## Related Tips

Excel Howtos

Excel Howtos

Excel Howtos

Excel Howtos

Excel Howtos

Excel Howtos

### 27 Responses to “Rounding time to nearest minute or quarter hour etc. [formulas]”

1. me says:

Wouldn't it be easier to multiply by 60*24, round it, and divide again?

• Jon Peltier says:

"Easier" in what sense? Not in terms of understanding the formula.

2. Jon Peltier says:

I find an alternative which is more clear to me is:

=MROUND(A1,TIMEVALUE("0:00:01"))
=MROUND(A1,TIMEVALUE("0:00:15"))
=MROUND(A1,TIMEVALUE("0:01"))
=MROUND(A1,TIMEVALUE("0:15"))
=MROUND(A1,TIMEVALUE("1:00"))

This clearly shows the nearest amount I'm rounding to. And I can instead put my rounding amount in another cell, like A2, and make it easier, without magic numbers in the formula:

=MROUND(A1,A2)

• MF says:

This is brilliant! 🙂

3. GraH says:

• GraH says:

Clearly this is not the solution for the home work.

4. Leonid says:

Regarding homework, please clarify what is expected answer for the value of 6/23/2017 17:21:00

5. Chetan says:

Hi Chandoo,
thanks for this post, on similar lines, i need help with Dates..

I need to cover DDMMYYYY to MMYYY, example:
Dates Amount Invested
1/Jan/2000 5000
15/Jan/2000 3000
15/Feb/2002 20000
20/Feb/2002 10000

In Excel this should look like:

Jan/2000 5000
Jan/2000 3000
Feb/2002 20000
Feb/2002 10000

basically i want to remove the DAY, as i want to create a Pivot out of this, to show a cumulative investment in a specific month, now if i run the pivot, all the days are being mentioned instead of cumulative for that month

• Pramod says:

Hi Chetan,

Use below formula to convert the date:

=TEXT(A1,"MMM/YYYY")

Assuming the date 1/Jan/2000 is in numerical format

• Herbert says:

Hi Chetan,
why not keep it simple and group the row labels in the pivot by Month?
To do this just right click on any date row label and select Group ... Months.

Cheers,
Herbert

6. John Jairo V says:

Hi, to all!

=WORKDAY(A1,N(MOD(A1,1)>18/24))+IF(MOD(A1,1)>18/24,9/24,MAX(CEILING(MOD(A1,1),1/24),9/24))

Blessings!

• mma173 says:

This does not take the weekend into account.

• John Jairo V says:

Chandoo says:

"round it up to nearest working hour."
"The working hours are 9AM to 6PM on weekdays (Monday – Friday)"

I assume that weekends should not be taken into account. Blessings!

• David N says:

I think mma173 was trying to say that your formula does not always round weekend times to the following Monday at 9:00 AM. For example, your formula rounds 06/24/2017 07:21:32 to 06/24/2017 09:00:00, which is still Saturday instead of Monday.

• John Jairo V says:

You're right!

Then, use this:

=MAX(CEILING(A1,1/24),WORKDAY(A1,--OR(WEEKDAY(A1,2)>5,MOD(A1,1)>18/24))+9/24)

Blessings!

• MF says:

=MAX(CEILING(A1,1/24),WORKDAY(A1,--OR(WEEKDAY(A1,2)>5,MOD(A1,1)>18/24))+9/24)

What an elagent solution! 🙂

• David N says:

Yes indeed; very nice! The only thing to watch for is the same rounding concern I noted in my own revised solution. If the daily cutoff was 5:00 PM, or 17/24 instead of 18/24, then a time of exactly 5:00 PM would end up incorrectly rounding to the next day.

7. David N says:

=IF(OR(WEEKDAY(A1,11)>5,A1-TRUNC(A1)>TIME(18,0,0)),WORKDAY.INTL(A1,1)+TIME(9,0,0),CEILING.MATH(A1,TIME(1+8*(HOUR(A1)<9),0,0)))

• David N says:

If 6:00 PM does not count as the next working hour (because the work day ends without completing that full hour), then my previous formula can be modified by changing TIME(18,0,0) to TIME(17,0,0).

Additionally, decimal rounding can sometimes get Excel into trouble when making very absolute equality/inequality comparisons, so it's probably best to forcibly round to a reasonable number of decimals. Taking 06/23/2017 17:00:00 as an example:

A1-TRUNC(A1) = 0.708333333335759
TIME(17,0,0) = 0.708333333333333

This makes it look like 5:00:00 is greater than itself, but rounding to 8 decimal places resolves that problem. Here is my modified formula.

=IF(OR(WEEKDAY(A1,11)>5,ROUND(A1-TRUNC(A1),8)>ROUND(TIME(17,0,0),8)),WORKDAY.INTL(A1,1)+TIME(9,0,0),CEILING.MATH(A1,TIME(1+8*(HOUR(A1)<9),0,0)))

8. Jason Morin says:

Sec. =ROUND(A1/("00:00:01"),0)*("00:00:01")
Min. =ROUND(A1/("00:01:00"),0)*("00:01:00")
Hr. =ROUND(A1/("01:00:00"),0)*("01:00:00")
15 Min. =ROUND(A1/("00:15:00"),0)*("00:15:00")

Etc.

9. Rajesh says:

What's is rounded off to the nearest number of months from 08/10/2016 to 31/03/2017.

10. Akash says:

4 hours 15mnts nearest hour

11. Jose L Barroso says:

Need help to get a formula to Rounding Time to nearest following 6 minutes Like example 1:53:27 need to be round up to 1:56:00.

• Chandoo says:

Assuming you have only time value in cell A1, use this formula:

= time(hour(a1), mround(minute(a1),6),0) will take it up to next 6th minute.

For nearest 6 minutes (that is first 2 min 59 seconds go down and next 3 mins 1 sec go up) use below formula instead:

=time(hour(a1), mround(minute(a1)+second(a1)/60-3,6),0)

• Jose L Barroso says:

It didn't work do you have file with an example?

• Jose L Barroso says:

Chandoo

Do you think you can help me with the formula I try use the formula but it didn't work

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.