Tom’s Tutorials For Excel: Rounding Times of Day by the Half Hour

Tom’s Tutorials For Excel: Rounding Times of Day by the Half Hour

Here are three pictures to show how a time of day can be rounded…
• To its nearest half-hour.
• Up, to its next half-hour.
• Down, to its last half-hour.

Picture #1 – Rounding a time to its nearest half-hour.
The formulas in range E3:F20 round their respective times in range B3:C20.
The formula in cell E3, which is copied across and down to cell F20 is
=ROUND(B3*48,0)/48


Picture #2 – Rounding a time up to its next half-hour.
The formulas in range E3:F20 round their respective times in range B3:C20.
The formula in cell E3, which is copied across and down to cell F20 is
=CEILING(B3,0.5/24)


Picture #3 – Rounding a time down to its last half-hour.
The formulas in range E3:F20 round their respective times in range B3:C20.
The formula in cell E3, which is copied across and down to cell F20 is
=FLOOR(B3,0.5/24)

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
17 comments on “Tom’s Tutorials For Excel: Rounding Times of Day by the Half Hour
  1. Sean says:

    Hi, Tom! I’m looking for a way to round a certain range of times to a set time. So, within 5 minues of 8:00 am: 7:55 am-8:05 am would automatically round to 8 am. 4:25 pm-4:35 pm would automatically round to 4:30 pm. Can you help?

  2. hi, tom! i want formula for hour. if any worker worker from 9.10 am to 5.45 pm then calculate automatically how many hour he/she worked. (with hour & minute)

    • Tom Urtis says:

      If your start time of 9:10 AM is in cell A1
      and
      your end time of 5:45 PM is in cell B1
      then
      in some other cell (let’s say cell C1) enter
      =B1-A1
      and
      custom format cell C1 as h:mm
      which would display 8:35
      which would mean 8 hours and 35 minutes.

  3. Loc Nguyen says:

    I want to find a way where for eg. The time I start work is 9:30. But I never go on the exact time. I always go earlier. I know how to use CEILING formula to round it up to the nearest 30 minutes if I go to work like 9:01, it rounds it to 9:30. Which is what I want. But now my issue is. I want a formula where if I go to work 8:56am or 8:40, it will still round up to 9:30

  4. Albert says:

    =Floor(B3 + 5/60,1/6/24)

    Add 5 minutes to each time and floor the result gets 7:56, 8:04 -> 8:00.

  5. Carlos says:

    Hi,

    I’m trying to round down over time hours. Everything should be in down to the hour examples
    1:56 – 1:00
    2:17 – 2:00

    I’m using floor currently but have come to a little problem. When it is exactly on the hour like 1:00, 2:00, 3:00, it rounds down to 0:00, 1:00, 2:00 when it shouldn’t. Any possible solution?

    Thanks!

  6. Maria Escobar says:

    Hi Tom,

    Here is my example: If an employee clocks in before 7:15 I need the time to round to 7:00 am and if they clock in after 7:15 I need the time to round to 7:30. How can I do this ?
    So for example if someone clocks in at 6:47 I need the time to round to 7:00 am or if the clock at 7:10 I need the time to round to 7:00 am as well.
    Thank you.

  7. Melissa Garza says:

    In time 6:56:00 AM
    Lunch out 12:00:00 PM
    Lunch in 12:30:00 PM
    Out Time 4:18:00 PM
    Tom,
    Please help!!!! I need a formula for this time to total to the nearest .25….so it should be 8.75 hours worked

    • Tom Urtis says:

      Please clarify if each time in and time out should be rounded separately, which would make a difference than if they were not and rounded at the end as a single aggregate number.

      • Melissa Garza says:

        there is a 7 minute so if we clock in at 6:52 it rounds to 6:45 am am if we clock out at 3:37 it rounds up to 3:45 pm .That is why i need it to round to quarter hours

Leave a Reply

Your email address will not be published. Required fields are marked *

*