Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statements help
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(B98,8,(IF(B9<8,B9))))). I currently have the following formula for figuring out the over time rate hours: =IF(B9=8,0,(IF(B98,(B9-8),(IF(B9<8,0))))) I do I adjust the formulas to incorporate the times of day? Thanks. Mandy Jo |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statements help
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) -- Regards Roger Govier "Mandy J.S." wrote in message ... 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(B98,8,(IF(B9<8,B9))))). I currently have the following formula for figuring out the over time rate hours: =IF(B9=8,0,(IF(B98,(B9-8),(IF(B9<8,0))))) I do I adjust the formulas to incorporate the times of day? Thanks. Mandy Jo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If, then statements | Excel Discussion (Misc queries) | |||
If then statements | Excel Discussion (Misc queries) | |||
If, Then Statements | Excel Worksheet Functions | |||
IF Statements (Mutliple Statements) | Excel Worksheet Functions | |||
if statements | Excel Discussion (Misc queries) |