Tom’s Tutorials For Excel: Rounding Elapsed Time by the Quarter-Hour

Tom’s Tutorials For Excel: Rounding Elapsed Time by the Quarter-Hour

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

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


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

The subtraction formula rounding to the nearest quarter-hour, entered in cell E3 copied down:
=ROUND((C3-B3)*96,0)

The subtraction formula rounding up to the next quarter-hour, entered in cell F3 copied down:
=CEILING((C3-B3)/(1/96),1)

The subtraction formula rounding down to the prior quarter-hour, entered in cell G3 copied down:
=FLOOR((C3-B3)/(1/96),1)

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
One comment on “Tom’s Tutorials For Excel: Rounding Elapsed Time by the Quarter-Hour

Leave a Reply

Your email address will not be published. Required fields are marked *

*