Calculating Overtime Hours from varying lists...

Hello!

I am trying to calculate the Normal, OT and nightshift. hours based on lists I will try to explain here.. and I have attached a sample

Formula for working hours in cell W6

If cell G6 = CW1 (list from sheet 3) then * sheet 3 C26 by O6 but only if it is up to and including 10 hours

I am unsure how to make the formula so it checks and returns for each of the CW rates on sheet 3??

If the above can be achieved I will need a formula also for the Overtime in Cell X6 this will be based on the above but only for hours above 10 in cell O6.

AND THEN! lol sorry

The nightshift also needs the calculation but will need to recognize that nightshift has been selected in cell D7 then using the overtime rates from whichever CW has been chosen in column G

Phew no wonder I am confused!!

Thanks for your help

Hi

W6 should equal $442 i.e. 10 hours at normal time from CW2 $44.20 sheet 3

and

X6 should equal $61.10 because it is only one hour overtime

Just to note also that when nightshift is in effect it is only for anything over 8 hours in O6 Monday to Friday.

For Normal 10 hours C column gives rates
For OT and Night shift which are the rates.