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

Posted on June 26th, 2017 in Excel Howtos - 22 comments

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)

Post your answers in the comments section. Tick tock, tick tock… time is ticking, post your answers.

### 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.

 SUMPRODUCT Vs. Power Query on Mt. KauKau How to add a lot of Goal Seeking to a model
 Written by Chandoo Tags: date and time, homework, Learn Excel, Microsoft Excel Formulas, mround, quick tip, round Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

### 22 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.

 SUMPRODUCT Vs. Power Query on Mt. KauKau How to add a lot of Goal Seeking to a model