I'm working on a time sheet where employees work a max of 80 hours in a two week period. Once they reach 80 hours, then OT kicks in. So, I'm trying to do the calculation for the OT...and created this formula =IF((N28-80)<80,SUM(N28-80),0) - which works great if they have 80 or more hours, but if less than 80, I'm getting a negative value in the field. What did I do wrong?
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Trouble with an Otherwise Zero formula
0
Answers
0
Try formula =IF(N2<=80,0,N2-80) in Row2. You need to break it down in sections, like if the cell in question is LessThan or Equal to 80, then there is 0 hours OT, ELSE compute the difference between hours worked and 80 hours to get the # of OT hours worked.
When in doubt, simplify things (take one component at a time!
~ Bueno suerte!
Discussion
Thank you...I kept working on it and just now found a calculation that also works...and was about to post it, when I saw your reply. I appreciate your help. This is what I did:
=SUM(N28-80)*IF((N28-80)<80,(N28>80),0)
Yours is simpler, so I might try it and see if it works.
Thanks!
=SUM(N28-80)*IF((N28-80)<80,(N28>80),0)
Yours is simpler, so I might try it and see if it works.
Thanks!
SchauerArts Nov 10, '16 at 3:38 pm
0
I did find a formula that works,
=SUM(N28-80)*IF((N28-80)<80,(N28>80),0)