Round Time to the Nearest Quarter Hour

October 29, 2001 - by Bill Jelen

Delaine asks:

I need to enter a series of time values in military time. Then, I need to total them up and round to the nearest quarter hour.

To enter values in military time, select those cells and then Format > Cells. Click the Number tab. Click Time in the left listbox. Select 13:30 as the number format. You can then enter the times.

To get a total in cell A99 of all the times, you first must format cell A99 to show hours in excess of 24. Select A99, Format > Cells. Click Custom in the left listbox. In the Custom Format box type: [h]:mm

You can now enter a formula to sum the times, something like =SUM(A2:A98). Since you want to round to the nearest quarter hour, use this formula instead: =ROUND(SUM(A2:A99)*96,0)/96

There are 96 quarter-hours in a day. Multiplying the total time by 96, rounding, and then dividing by 96 will give you the nearest quarter hour.