Timesheet w/ if statement to add overtime

[jjcogar]

I'm trying to figure out the formula for a timesheet. I have one rate for "drive time", another rate for "service time". But if the start time is after 17:00, then it's an overtime rate. Then Saturday/Sunday rate creates another variable. I've looked through the posts and can't find a formula that works for this situation. Will one formula work for all these, or should I separate them out? Any ideas?

Variables I'm using:
a=date b=n/a c=n/a

d=drive start time e=arrival time f=total drive time


g=service start time h=service end time i=total service time

 

[wnoppers]

Can you provide a "sample" of the data as it sits on the spreadsheet, and any formulae you are currently using?

 

[Paella1]

So if it's after 17:00, is the rate a multiple of the rate that it would otherwise be? Or is the OT rate the same regardless of whether it is drive time or service time?

 

[jjcogar]

A B C D E F G H I J k
date Store Tracking Begin Arrival Total Begin End Total O/T xtra
# Drive Time Drive Job Job Job Hrs Hrs
Time Time
1 wkday shop 7:45 8:30 :45 8:30 11:30 3:00 0 0
2 wkday dump 11:30 11:45 :15 11:45 13:00 1:15 0 0
3 wkday flood 16:00 17:00 1:00 17:00 20:00 3:00 3 0
4 wkend break 11:00 11:45 :45 11:45 15:00 3:15 0 3:15


Formula I'm using to calculate column f is:
=IF(D17-E17>TIME(8,0,0),TIME(17,0,0)-D17,E17-D17)

I tried using: D17-E17-F17 to figure the O/T, but it came up with 0.

 

[jjcogar]

Sorry the spacing got messed up on my sample, I'm not sure that's very helpful or understandable.

The overtime rate is 1.5 the rate of drive time and 1.5 the rate of job time.