All Posts

Excel Formula for Employee Hours Tracking

2026-03-18

Hours Worked (Time Format)

If start time is in A2 and end time in B2 (both as time values):

=(B2-A2)*24

Handle Overnight Shifts

If the shift crosses midnight:

=IF(B2<A2, (B2+1-A2)*24, (B2-A2)*24)

Weekly Overtime Hours

Calculate overtime (hours over 40 in a week). Weekly total in C2:

=MAX(0, C2-40)

Total Pay with Overtime

Regular rate in D2. Overtime is 1.5x:

=IF(C2>40, 40*D2+(C2-40)*D2*1.5, C2*D2)

Generate formulas instantly

Describe what you need in plain English. Get the formula for Excel, Google Sheets, or Airtable.

Try FormulaWiz Free