Forum Discussion
HELP WITH FORMULA IF, AND
Hello - I am looking for basic excel formula help for an equation. What I need the cell to ultimately do is calculate overtime hours. So when I add the total hours (which are REGULAR + FACILITY + STBY +QT), then subtract 40 IF the total is OVER 40 it generates the OT hours and if LESS than 40 it generates 0 (because there the sum can not be in the negative. Here is what it looks like in the spreadsheet:
I know its simple but I'm struggling with it so asking for assistance.
THANKS FOR ANY HELP ON THIS
9 Replies
- IF(MAX(A2-40,0)>40,MAX(A2-40,0),0)
Hope this help!Donald_Genes_ Thanks for getting back to me on this. My formula skills are sub par but I am working on it. I did try yours, however it's not generating the final outcome I am hoping for yet. In the screenshot you will see that Column K is for OT(overtime - anything above 40 hours) but only after you take the total from Column F and subtract the STANDBY hours (column I) and the standard 40 (hours in a work week). Then if the final number is less than 0 it should generate 0 and over 0 should have how ever many hours of OT they will receive. See screenshot:
Thank you in advance for any guidance on this.
THANK YOU! I had it in there earlier and tried it out for what I needed and I made an adjustment, but for some reason its not populating what i need again. The K (OT) column should be F-I-40. And then if its over 0 then generate that amount and under 0 then read 0.
Think you can give me one more shot on this??
- Thanks for getting back to me on this. My formula skills are sub par but I am working on it. I did try yours, however it's not generating the final outcome I am hoping for yet. In the screenshot you will see that Column K is for OT(overtime - anything above 40 hours) but only after you take the total from Column F and subtract the STANDBY hours (column I) and the standard 40 (hours in a work week). Then if the final number is less than 0 it should generate 0 and over 0 should have how ever many hours of OT they will receive. See screenshot:
Thank you in advance for any guidance on this.CHEEGOMOON Try this:
=MAX(0,F2-I2-40)
