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

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)

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
2 comments on “Tom’s Tutorials For Excel: Rounding Elapsed Time by the Hour
  1. Pat Constant says:

    Tom

    Excellent, but can you help me to do the same when employee finishes work after midnight???

    Pat

    • Tom Urtis says:

      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.

Leave a Reply

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

*