Sumif Weekday Help

Hi

I need help with a formula that will calculate if it contains a weekday or weekend.

In column A I have dates that are formatted to show as Fri/14/10/11 (ddd/dd/mm/yy).
In column C I have hours worked by a staff member
At the bottom of the sheet i have the hourly rates for working (Mon-Fri), (Sat) and (Sun).

What I want at the end of each row is a formula that says if the date (Column A) is between and Mon and Fri, then multiply the hours worked (Column C or D) by the hourly rate. If the date is a Saturday then follow the same process but with a different hourly rate and if the date is a Sunday then again same process but different hourly rate.

Is this at all possible or am I being too adventurous?

Something like =A1*LOOKUP(WEEKDAY(A2,2),{0,5,6,7},{"r1","r2","r3"})

where A1 is hours worked
A2, the date
r1, etc.. the different rates

Thanks for that. I have put it in and it's given me £108.60 when the cost should be £83.55. (they worked 7.5hrs on a Friday with a rate of £11.14)

Just noticed that the £108.60 is the what the cost would have been if worked on a Saturday.

Thanks alansidman, that worked a treat! Well for one row anyway, just hope it works for the others.

Thank you again

This should be better

=LOOKUP(WEEKDAY(A2,2),{0,6,7,8},{"r1","r2","r3"})

Thanks all for your help. I still have the formula in situ which works great:

=If(Weekday(A1,2)=6, B1*SaturdayRate, if(Weekday(A1,2)=7,B1*SundayRate, B1*WeekdayRate))

But i now need to make it more complicated! :-(

I have shift date in Column A (ddd/dd/mm/yy)
I have shift worked in Column B (E=Early, L=Late, N=Nights)
I have the hours worked in Column C

If a staff member works Mon-Fri they get one rate, Saturday a different rate and sunday a different rate again. They also get the Saturday rate if they work nights.

What I want the formula to now do is check what day it is and also check what shift it is. So if they worked a Monday they would get basic rate but if it was a Monday night they would get night rate.

Is this making it too complicated?

Hi

That kind of works, but when i put a Saturday date, with an Early shift, the cost of the shift shows at basic rate not the saturday rate.

Sorry, just re-looked at the formula, i forgot to delete the ",2" at the first part of the formula. D'oh!

I've been doing this piece of work all day, think it's time a left it now.

However, the formula you gave above works great. Thanks for your help.