Tom’s Tutorials For Excel: Rounding Elapsed Time by 5 Minutes

Tom’s Tutorials For Excel: Rounding Elapsed Time by 5 Minutes

Here are 3 formulas that round an elapsed time by the 5-minute mark, in terms of nearest 5-minute mark, up to the next 5-minute mark, and down to the last (lower) 5-minute mark.

In the picture, notice the differences for each employee’s elapsed time in decimalized 5-minute segments, depending on if, and how, you decide to round or not round. The number 288 in the formulas relates to the count of 5-minute segements that comprise a full calendar day.


The formula that subtracts the raw difference, entered in cell D3 and copied down:
=(C3-B3)*288

The subtraction formula rounding to the nearest 5-minute mark, entered in cell E3 and copied down:
=ROUND((C3-B3)*288,0)

The subtraction formula rounding up to the next 5-minute mark, entered in cell F3 and copied down:
=CEILING((C3-B3)/(1/288),1)

The subtraction formula rounding down to the prior 5-minute mark, entered in cell G3 copied down:
=FLOOR((C3-B3)/(1/288),1)