Forum Discussion
Creating a formula to calculate payroll with multiple conditions
If an employee worked less than 40 hours, then hours need to be multiplied by the rate plus diving hours, double time hours, and per diem hours, than if the employee worked more than 40 we need t...
what you typed didn't work because it has the wrong structure. Get rid of the "G5<40," in the middle and I think the >=40 should be <40 in the beginning, and it should work.
=IF(G5<40, G5*$D5+H5*$E5+I5*($D5*2)+J5*$F5, $D5*40+(($D5*1.5)*(G5-40))+H5*$E5+I5*($D5*2)+J5*$F5)
that said it could be done 'easier' / less messy like this:
=$D5*$G5+$D5*0.5*MAX(0,$G5-40)+$H5*$E5+$I5*($D5*2)+$J5*$F5
=IF(G5<40, G5*$D5+H5*$E5+I5*($D5*2)+J5*$F5, $D5*40+(($D5*1.5)*(G5-40))+H5*$E5+I5*($D5*2)+J5*$F5)
that said it could be done 'easier' / less messy like this:
=$D5*$G5+$D5*0.5*MAX(0,$G5-40)+$H5*$E5+$I5*($D5*2)+$J5*$F5
m_tarler thank you so much. you saved me hours and hours.
