Round Time to the Nearest Quarter Hour
October 29, 2001 - by Bill Jelen
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:
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.