timesheet and overtime formula/s

[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

 

[Eduardo]

Hi,
look into CPearson Web

http://www.cpearson.com/excel/overtime.htm

if this helps please click yes, thanks

 

[Fred Smith]

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

 

[FASTWRX]

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