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

[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.

 

[Ron Rosenfeld]

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.

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

 

[T. Valko]

If 40 hours is the normal amount for regular hours:

A1 = 45

Regular hours: =MIN(40,A1)

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