Hi Guys
I have the basic formulas however there are some tricky areas I can't seem to get to work for me
This is the normal Mon to Fri day shift formula
=24*(IF($D$6>$H$6,$H$6+1-$D$6,$H$6-$D$6))
Is working just fine as it always has where D6 is the start time for their shift and H6 is their finish time
Mon to Fri Night shift wherethe night shift penalties start after 10pm
=COUNTIF($D$6,">=22:00")*24*(IF($D$6>$H$6,$H$6+1-$D$6,$H$6-$D$6))
Again this works fine for me
However if there is a public holiday I have tried this mix of formulas but obviously it doesn't work
=IF('Roster week 1'!E5,">=P/H"),IF($D$6,">=22:00")*24*(IF(D6>H6,H6+1-D6,H6-D6))
All the above formulas are recorded in different Cells and there are different pay rates for the different times
I am hoping there is a simple way to marry all the formulas together
and that it can be added to ALL the above formulas if needed
Cheers
Bill
I have the basic formulas however there are some tricky areas I can't seem to get to work for me
This is the normal Mon to Fri day shift formula
=24*(IF($D$6>$H$6,$H$6+1-$D$6,$H$6-$D$6))
Is working just fine as it always has where D6 is the start time for their shift and H6 is their finish time
Mon to Fri Night shift wherethe night shift penalties start after 10pm
=COUNTIF($D$6,">=22:00")*24*(IF($D$6>$H$6,$H$6+1-$D$6,$H$6-$D$6))
Again this works fine for me
However if there is a public holiday I have tried this mix of formulas but obviously it doesn't work
=IF('Roster week 1'!E5,">=P/H"),IF($D$6,">=22:00")*24*(IF(D6>H6,H6+1-D6,H6-D6))
All the above formulas are recorded in different Cells and there are different pay rates for the different times
I am hoping there is a simple way to marry all the formulas together
and that it can be added to ALL the above formulas if needed
Cheers
Bill
