Rounding Time in Excel

May 30, 2004 - by Bill Jelen

Robert writes,

I need a formula that can round a time to the nearest quarter hour. If someone clocks in at 7:07, it counts as 7AM. If they clock in at 7:08, then it would be 7:15 AM.

Excel stores times in an interesting way. Each 24 hour period is equivalent to the number 1. 6AM is actually stored as 0.25. Noon is 0.5. 6PM is 0.75. We see a time on the Excel spreadsheet because the cell is formatted with a time format.

With 24 hours in a day, there are actually 96 quarter-hour period in each day. Without the Analysis Toolpack installed, the method to round a cell to the nearest 1/96th is this formula:

=ROUND(A1*96,0)/96

If your times are in column A, you would enter a formula like this in column B. You will have to use Format > Cells and then format the cells in column B with a time format.

Another option is to use the Analysis Toolpack. This add-in ships with Excel but is not always installed on every machine. To enable the Analysis Toolpack, go to Tools > AddIns and choose Analysis Toolpack from the list. Once the Toolpack is installed, you can then use the =MROUND() function. This function will round a number to the nearest multiple. In Robert’s case, this would be rounding to the nearest 1/96th of a day. The formula would be =MROUND(A1,(1/96)).

Either method will work. I tend to use the prior method because it will work even if someone doesn’t have the analysis toolpack enabled.