timesheet and overtime formula/s

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
 
F

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
 
F

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top