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.

round-time-in-excel

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.

Written by Chandoo
Tags: , , , , , ,
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?

  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)

  3. GraH says:

    what about MROUND(A1,time(0,0,15))?

  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!

    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!

    • 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:

    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)))

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

Leave a Reply