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
B2 | Planned net time | 480 min |
|---|---|---|
C2 | Actual net time | 510 min |
D2 | Actual − planned | 0.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
- Enter planned net time in
B2and actual net time inC2as numbers in minutes. Type480and510for this example. Deduct unpaid breaks from both durations before entering them. - Paste the formula into
D2.C2-B2gives the difference in minutes. Dividing by 60 converts it to decimal hours. - Format
D2as 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.