Forum Discussion

Alexandra109's avatar
Alexandra109
Copper Contributor
Apr 03, 2024

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 to calculate regular hours plus other time hours and diving hours, double time hours and per diem hours. Please help. What ever I created doesn't work.

excel
Formulas and Functions
  • m_tarler's avatar
    m_tarler
    Steel Contributor
    Apr 03, 2024
    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

Resources