How to Round Time to Hours or Minutes in Excel Up or Down

Often when working with time in Excel, there is a need to round time to specific intervals. For example, if a consultant is billing time, they may round 15 minutes up or 30 minutes down.



Examples of Rounding Time to 15-Minute and Half-Hour Intervals in Excel

The image below shows how you can round time to 15- or 30-minute intervals. The formula in cell D2 looks like this:

=ROUNDUP(C2*24/0.25,0)/(24/0.25)
Examples of rounding time.

The following formula in cell E2 looks as follows:

rounding time to 30-minute intervals.

You can round time to the nearest hour by multiplying it by 24. Then, the result of the calculation is placed in the first argument of the ROUNDUP function. After that, divide the result by 24. For example, the formula below returns the value 7:00:00:

=ROUNDUP(D2*24/0.2,0)/(24/0.5)
round to the nearest hour.

Download examples on how to round time to hours or minutes in Excel download file

To round time to the nearest 15-minute interval upwards, divide all numbers by 24 and then by 0.25 (quarter-hour).

The formula below returns 6:30:00:

=ROUNDUP("6:15:27"*24/0.25,0)/(24/0.25)

To round time down to a 30-minute interval, use the ROUNDDOWN function and divide by 24 and then by 0.5 (half-hour). The following formula returns 6:00:00:

=ROUNDDOWN("6:15:27"*24/0.5,0)/(24/0.5)

As shown in these examples, rounding time is slightly more complex than rounding fractional numbers or dates. However, once you understand the process, you can handle any similar task with ease.