Tom’s Tutorials For Excel: Rounding Times of Day by 5 Minutes

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)

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
2 comments on “Tom’s Tutorials For Excel: Rounding Times of Day by 5 Minutes
  1. Sohail Merchant says:

    Hi.

    Please explain logic behind 288 and 144

    Thanks

    • Tom Urtis says:

      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.

Leave a Reply to Sohail Merchant Cancel reply

Your email address will not be published.

*