M
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
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