Tom’s Tutorials For Excel: Rounding Elapsed Time by the Hour
Here are 3 formulas that round an elapsed time by the hour, in terms of nearest hour, up to the next hour, and down to the last (lower) hour.
In the picture, notice the differences for each employee’s elapsed time in decimalized hours, depending on if, and how, you decide to round or not round. The number 24 in the formulas relates to the count of hours that comprise a full calendar day.
The formula that subtracts the raw difference, entered in cell D3 and copied down:
=(C3-B3)*24
The subtraction formula that rounds to the nearest hour, entered in cell E3 and copied down:
=ROUND((C3-B3)*24,0)
The subtraction formula that rounds up to the next hour, entered in cell F3 and copied down:
=CEILING((C3-B3)/(1/24),1)
The subtraction formula that rounds down to the prior hour, entered in cell G3 and copied down:
=FLOOR((C3-B3)/(1/24),1)
Tom
Excellent, but can you help me to do the same when employee finishes work after midnight???
Pat
Suppose your start time is in cell A1 and your end time is in cell B1 for the next calendar date.
For raw elapsed time in hours and minutes, enter the formula
=MOD(B1-A1,1)
and custom format that cell as [h]:mm.
To round the difference to the nearest hour, enter the formula
=ROUND((B1-A1)*24,0)+24
and format as General.
To round the difference up to the next hour, enter the formula
=CEILING((B1-A1)/(1/24),1)+24
and format as General.
To round the difference down to the prior hour, enter the formula
=FLOOR((B1-A1)/(1/24),1)+24
and format as General.