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)
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?
What should happen at 7:56 AM or 4:37 PM?
I have the same question
If 7:56 round to 8:00am
& 4:37pm round to 4:30pm ??
See my blog post here:
https://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-rounding-times-of-day-by-the-quarter-hour/
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)
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.
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
In your case, with 8:55 or 8:40 or 9:10 or 8:31 entered in cell A1, this formula would return 9:30.
=CEILING(A1,0.198)
=Floor(B3 + 5/60,1/6/24)
Add 5 minutes to each time and floor the result gets 7:56, 8:04 -> 8:00.
Isn’t your formula a lot of extra calculations that arrive at the same result?
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!
You did not say what formula you were using but this works for me:
=FLOOR(A2,1/24)
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.
If I understand correctly, see if this formula works:
=MROUND(A2,1/48)
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
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.
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