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

###### 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?

• Tom Urtis says:

What should happen at 7:56 AM or 4:37 PM?

• Fung says:

I have the same question
If 7:56 round to 8:00am
& 4:37pm round to 4:30pm ??

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

• Tom Urtis says:

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)

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.

• Tom Urtis says:

Isn’t your formula a lot of extra calculations that arrive at the same result?

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!

• Tom Urtis says:

You did not say what formula you were using but this works for me:

=FLOOR(A2,1/24)

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.

• Tom Urtis says:

If I understand correctly, see if this formula works:
=MROUND(A2,1/48)

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