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.
- Working with date & time values in Excel – a quick intro
- Convert fractional time to hours & minutes
- Highlighting over due items
- 42 tips for Excel time travelers – calculating past, present and future time values using formulas
- Sorting by birthday
- More date & time tips
27 Responses to “Rounding time to nearest minute or quarter hour etc. [formulas]”
Wouldn't it be easier to multiply by 60*24, round it, and divide again?
"Easier" in what sense? Not in terms of understanding the formula.
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)
This is brilliant! 🙂
what about MROUND(A1,time(0,0,15))?
Clearly this is not the solution for the home work.
Regarding homework, please clarify what is expected answer for the value of 6/23/2017 17:21:00
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
Hi Chetan,
Use below formula to convert the date:
=TEXT(A1,"MMM/YYYY")
Assuming the date 1/Jan/2000 is in numerical format
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
Hi, to all!
Lets start with:
=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!
This does not take the weekend into account.
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!
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.
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!
=MAX(CEILING(A1,1/24),WORKDAY(A1,--OR(WEEKDAY(A1,2)>5,MOD(A1,1)>18/24))+9/24)
What an elagent solution! 🙂
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.
[…] http://chandoo.org/wp/2017/06/26/rounding-time-in-excel/ […]
How about this?
=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)))
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)))
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.
What's is rounded off to the nearest number of months from 08/10/2016 to 31/03/2017.
4 hours 15mnts nearest hour
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.
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)
It didn't work do you have file with an example?
Chandoo
Do you think you can help me with the formula I try use the formula but it didn't work