Trying to calculate hours worked on timecard using "IF" command

[Melissa]

I have total hours in cell M11. In cell M13 I have a formula calculating
overtime hours: =IF(M11>40,M11-40,0). What I'm trying to do is cause the
cell with this formula to calculate the hours ONLY if the overtime hours are
over 40 AND outside of PTO and/or Holiday hours.

Here's an example:

total hours (cell M12)= 42.33
overtime hours (cell M13)= 2.33
PTO hours (cell M14) = 8.00
Holiday hours (cell M15) = 8.00

Total work hours INCLUDES pto and holiday hours, so overtime hours should
NOT be showing 2.33, but rather zero.

How do I create/modify my current formula (above) to calculate this properly?

Thanks!

Melissa

 

[Mike H]

Maybe this

=MAX(M11-(M14+M15)-40,0)

Mike

 

[Melissa]

Ok, but if the 42.33 hours WERE regular worked hours (not including PTO or
holiday), how do I make the formula look right with the other part of the
formula {eg =IF(M11>40,M11-40,0)}? Where do I put all the parethesis,
commas, etc? How do I put both formulas together so that both calculations
take effect?

 

[Shane Devenshire]

Hi,

Try

=IF(M11-M14-M15>40,M11-M14-M15-40,0)

 

[Melissa]

Shane,
Thank You, Thank You!! Your formula worked! You've just saved my bacon!

Melissa

 

[Mike H]

Hi,

You clearly never tried this formula which returns exactly the same answer
as Shane's much longer version.

Mike

 

[Shane Devenshire]

Glad to help, and thanks for the feedback.

Cheers,
Shane Devenshire