ExcelFlux

Compare planned and actual working hours

Subtract planned net work time from actual net work time. A shift planned for 480 minutes that takes 510 minutes runs 0.50 hours, or 30 minutes, over plan.

Worked example

B2Planned net time480 min
C2Actual net time510 min
D2Actual − planned0.50 hours

Copyable formulas

English Excel · en-US
=IF(COUNT(B2:C2)=2,(C2-B2)/60,"")
Turkish Excel · tr-TR
=EĞER(BAĞ_DEĞ_SAY(B2:C2)=2;(C2-B2)/60;"")

How to use the formula

  1. Enter planned net time in B2 and actual net time in C2 as numbers in minutes. Type 480 and 510 for this example. Deduct unpaid breaks from both durations before entering them.
  2. Paste the formula into D2. C2-B2 gives the difference in minutes. Dividing by 60 converts it to decimal hours.
  3. Format D2 as a Number with two decimal places. Copy the formula down for other shifts with the same column layout.

How to read the result

With 480 minutes planned, 510 minutes worked gives 0.50 hours, 450 minutes gives −0.50 hours, and 480 minutes gives 0.00 hours. The sign shows whether the shift ran over or under plan. Payroll overtime depends on separate rules.

Missing entries and zero hours

The formula leaves the result blank if either duration is missing. Enter a numeric 0 for actual time when the record is complete and no time was worked. With 480 minutes planned, that gives −8.00 hours. Text such as 510 min leaves the result blank.

To report actual net hours for an entire team, see how to sum working hours by team and month.

Microsoft Excel reference