timesheet and overtime formula/s

  • Thread starter Thread starter Andy Bolger
  • Start date Start date
A

Andy Bolger

Col D5: Start time
Col E5: Finish time
Col F5: Ordinary Hours

Above are all worked out, my problem is in calculating overtime
automatically and placing in:

Col H5: I want time and a half (first 3 hours)
Col I5: I want double time (after first 3 hours)

Many thanks
 
How do you calculate overtime? Assuming it's anything over 8 hours, in G5
use:
=max(f5-8,0)

Time and a half hours are:
=min(3,g5)

Double time hours are:
=max(0,g5-3)

Regards,
Fred
 
I'm providing two solutions that depend on whether the overtime is on top of
the normal hours, or if this is overtime alone.

If overtime alone. (all time is either *1.5 or *2)

F = E-D
H = (if((E-D)>3,3,(E-D)))*1.5
I = ((E-D)-3)*2

If 40 hours is considered in the mix:
F = E-D
H = (if((E-D)>43,3,(E-D)-40))*1.5
I = ((E-D)-43)*2
 
Back
Top