Rosters
Posted by David on October 15, 2001 3:13 AM
is there a formula where if you type a persons name you would get what they are making per hour times the number of hours worked in the total for the day. For example Stewart makes 10.00 per hour, Kathy 15.00, Steve 12.00 etc. If I had a roster that was done fortnightly can I get a calculation to find out the total per day by all persons entered for that day. The tricky part is that Saturdays the pay is +25%the hourly rate, Sunday +50%, holidays +150% and between the hours of 19:00-7:00 +15%.
Some people may only work two hours a day and others three etc. Any solutions? In a given day you may have up to five person working at different hourly rates.
Posted by IML on October 15, 2001 7:30 AM
Lets say you have the following data tables:
In a range I1:J10 list employees in column I and Pay in J. In K1:K10 list your holidays.
Let say you have headers in row 1 as follows Col A, Date, Col B employee, col C start time, col d stop time, col e Hours worked, column F Pay
On row 2 enter in the data for columns A through D.
Column E is calculated by the formula
=+(D2-C2)*24
The formula for your pay column would be
=VLOOKUP(B2,$I$1:$J$10,2,FALSE)*(E2+MAX(((WEEKDAY(A2)=7)*0.25),((WEEKDAY(A2)=1)*0.5),ISNUMBER(MATCH(A2,$K$1:$K$10,0))*1.5))+((D2-TIMEVALUE("19:00"))*(D2-TIMEVALUE("19:00")>0)+((TIMEVALUE("7:00")-C2))*(TIMEVALUE("7:00")-C2>0))*24*VLOOKUP(B2,I1:J10,2,FALSE)*0.15
I'd take a close look at working overtime hours (after 19:00 or before 7:00) on holidays and weekends to make sure it is what you want. I assumed holidays that fell on sat or sun would just be paid at the holiday rate.
It should go without saying, but be sure to test this on several of your scenarios against your desired results.
Good luck
Posted by IML on October 15, 2001 7:39 AM
revision:
change the long formula to
=VLOOKUP(B2,$I$1:$J$10,2,FALSE)*(E2*(1+MAX(((WEEKDAY(A2)=7)*0.25),((WEEKDAY(A2)=1)*0.5),ISNUMBER(MATCH(A2,$K$1:$K$10,0))*1.5)))+((D2-TIMEVALUE("19:00"))*(D2-TIMEVALUE("19:00")>0)+((TIMEVALUE("7:00")-C2))*(TIMEVALUE("7:00")-C2>0))*24*VLOOKUP(B2,I1:J10,2,FALSE)*0.15
