fbpx
Search
Close this search box.

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

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

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

Excel formula list - 100+ examples and howto guide for you

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.

Advanced Pivot Table tricks

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.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

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?

  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.

  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)

Leave a Reply