IF statements help

[Mandy J.S.]

Ok, I am working in MS Excel 2002 in Windows XP Professional. I am
trying to automate our service report calculations. This is for my
guys that go out on service calls. Here's what I am trying to work
with:

For service work performed Monday through Friday, up to eight(8)
hours per day, between the hours of 6:00 a.m. to 6:00 p.m. the rate
is $XXX

For service work performed over eight (8) hours or between 6:00 p.m.
and 6:00 a.m. Monday through Friday and all hours on Saturday the
rate is $XXXX.

For service work performed on Sundays and legal holidays the rate is
$XXXX

I have each day of the week in a separate column and the rows have
what time they clocked in and out.

So, I have row descriptions in column A, Monday in column B, Tuesday
in column C, until I have the rates in column I. The rates will be
put in individually because this can vary according to the particular
job.

My row headings are Departure Time, Time In, Break Time (decimal),
Time Out, Return Time, Total Time @ Customer, Total Travel Time,
Total Hours, Total Hours @ Regular Rate, and Total Hours @ Overtime
Rate.

I currently have the following formula for figuring out the regular
rate hours: =IF(B9=8,8,(IF(B9>8,8,(IF(B9<8,B9))))).

I currently have the following formula for figuring out the over time
rate hours: =IF(B9=8,0,(IF(B9>8,(B9-8),(IF(B9<8,0)))))

I do I adjust the formulas to incorporate the times of day?

Thanks.

Mandy Jo

 

[Roger Govier]

Hi Mandy

To give a definitive answer on your calculations needs some more
information.
Saturdays and Sundays are easy because all hours are at the higher rate.

From Monday to Friday, are you paying based on Total hours including travel
time?
Is break time deducted for the total time?
What if the travel time is outside normal hours and the working time (total
time?) exceeds 8 hours, does the operator get paid overtime for the hours
over 8 and extra because of the antisocial hours?

I will give you my suggestions.
In cell K1 enter 6:00 Am and in L1 enter 6:00 PM
in cell A12 enter "Antisocial Hours" and in cell B12 enter
=($K$1-B2)*24+(B6-$L$1)*24
Your formula in B10 can be simplified to
=MIN(8,B9)
Your formula in B11 can be simplified to
=MAX(0,B9-8)

In cell A13 enter "Hours Overtime to pay" and in B14
=MAX(B11,B12)