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)
The following formula in cell E2 looks as follows:
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)
Download examples on how to round time to hours or minutes in Excel
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.