hi there
I have a formula that works that calculates overtime rates depending on what variables you enter for time/half or double time to start.
eg would be an 8 hour working day with time?half from 8-10 hour then double time after that.
=SUM($AZ10*BB10)+(IF(INT(BA10)+(BA10-INT(BA10))/0.6<$E$72,0,IF($E$72<$E$74,MAX(0,(INT(BA10)+(BA10-INT(BA10))/0.6-$E$72-($E$74-$E$72)))*2+(IF((BA10-$E$72)<($E$74-$E$72),(INT(BA10)+(BA10-INT(BA10))/0.6-$E$72),($E$74-$E$72)))*1.5,(INT(BA10)+(BA10-INT(BA10))/0.6-$E$72)*2))*$FY10)*BB10
AZ10 = daily rate - user entered
BB10 = no of days - user entered
BA10 = No of hours in a day - user entered
E72 = base daily rate (after time/half starts)
E74 = double time starts
FY10 = hourly rate (daily rate/ base hourly day ie 8hr)
does anyone know of a way of condensing/simplifying this formula?
Johnny
I have a formula that works that calculates overtime rates depending on what variables you enter for time/half or double time to start.
eg would be an 8 hour working day with time?half from 8-10 hour then double time after that.
=SUM($AZ10*BB10)+(IF(INT(BA10)+(BA10-INT(BA10))/0.6<$E$72,0,IF($E$72<$E$74,MAX(0,(INT(BA10)+(BA10-INT(BA10))/0.6-$E$72-($E$74-$E$72)))*2+(IF((BA10-$E$72)<($E$74-$E$72),(INT(BA10)+(BA10-INT(BA10))/0.6-$E$72),($E$74-$E$72)))*1.5,(INT(BA10)+(BA10-INT(BA10))/0.6-$E$72)*2))*$FY10)*BB10
AZ10 = daily rate - user entered
BB10 = no of days - user entered
BA10 = No of hours in a day - user entered
E72 = base daily rate (after time/half starts)
E74 = double time starts
FY10 = hourly rate (daily rate/ base hourly day ie 8hr)
does anyone know of a way of condensing/simplifying this formula?
Johnny