Tom’s Tutorials For Excel: Rounding Times of Day by 5 Minutes
Here are three pictures to show how a time of day can be rounded…
• To its nearest 5-minute mark.
• Up, to its next 5-minute mark.
• Down, to its last 5-minute mark.
Picture #1 – Rounding a time to its nearest 5-minute mark.
The formulas in range E3:F20 round their respective times in range B3:C20.
The formula in cell E3, which is copied across and down to cell F20 is
=ROUND(B3*288,0)/288
Picture #2 – Rounding a time up to its next 5-minute mark.
The formulas in range E3:F20 round their respective times in range B3:C20.
The formula in cell E3, which is copied across and down to cell F20 is
=CEILING(B3,0.5/144)
Picture #3 – Rounding a time down to its last 5-minute mark.
The formulas in range E3:F20 round their respective times in range B3:C20.
The formula in cell E3, which is copied across and down to cell F20 is
=FLOOR(B3,0.5/144)
Hi.
Please explain logic behind 288 and 144
Thanks
Excel stores times as fractional days, and there are 288 5-minute slots in a day, which explains why it might be any of those 5-minute time slots for the first formula looking for the nearest 5-minute slot. The other two formulas round to the next or last 5-minute time slot, and because the result will be either next or last, it could not be both possibilities for the same time value being evaluated. Only half of the 288 time slots are possible and half of 288 is 144.