How do I separate and calculate a value in a cell with an IF

  • Thread starter Thread starter Peaches
  • Start date Start date
P

Peaches

I am trying to calculate one cell with an hourly worked value (45), and want
to also calculate overtime from the same cell value. How do I accomplish
this? Can I use a nested IF statement or is this possible. If not do I use a
formula. I do not have a separate cell for hours worked, and a separate cell
for overtime they are included in the same cell in which I have to calculate
both scenarios.
 
I am trying to calculate one cell with an hourly worked value (45), and want
to also calculate overtime from the same cell value. How do I accomplish
this? Can I use a nested IF statement or is this possible. If not do I use a
formula. I do not have a separate cell for hours worked, and a separate cell
for overtime they are included in the same cell in which I have to calculate
both scenarios.
Click to expand...

If your Full Time hours is 40,
Salary is the Hourly Rate and
OTFactor is the multiplier of Salary for Overtime (e.g. 1.5) then

Regular Pay:

=Salary*MIN(40,HrsWrkd)

Overtime Pay:

=Salary*OTFactor*MAX(0,HrsWrkd-40)


Total Pay:

=Salary*MIN(40,HrsWrkd)+Salary*OTFactor*MAX(0,HrsWrkd-40)

--ron
 
If 40 hours is the normal amount for regular hours:

A1 = 45

Regular hours: =MIN(40,A1)

OT hours: =MAX(0,A1-40)
 
Back
Top