Nested if

[trebor1956]

Hi,
I am trying to write a formula that works out overtime rates. I have a staff member on 25 hours/wk. Until he has worked 37 hours all is plain time, after that it depends on the day, Mon-Fri time+qtr, sat, Sun and bank hols Time+half. The "day of the week" is in col "A", if its a Bank hol this is indicated with a "Y" in col "B", I record his o/t hours worked in Col "C", keep a cumulo total in col "D" and the pay rate is displayed in col "E". When the cumulo total is greater than 12 (37-25) the o/t rate needs to reflect what day it has been worked.
Seems quite straight forward when I write it down but I can't get the formula to work.

 

[ranman256]

i dont use a formula, instead I run a macro.
the sheet is sorted by person,then time/hrs

it scans the records, gets a person, sums the hrs for RegTime.
once that limit hits, starts summing OT.

a formula would be really complex.

 

[trebor1956]

I can't see how this would work as any overtime over 12 hours could still be at differing rates. This is why based on a number of conditions I would like to work out the rate for each separate overtime event.
I hoped that by using an 'if' statement to catch the cumulo time worked when this reached 12 then further if statements would apply the o/t rate dependent on the day of the week and/or if it is a bank hol.