1

I'm trying to set up a simple bi-weekly paycheck estimator in Google Sheets. Basically you put in the hours worked for Week 1 and Week 2, and I want it to automatically determine the amount of overtime for each week (hours over 40). I'm trying to do this with if/and statements but just can't seem to get the formatting/arguments correct.

Example of what I'm trying to do: A1 (Week1) = 39, and A2 (Week 2) is 45. It should determine no OT for week 1, but 5 hours for week two, and list a total of 5 OT hours in cell A3. Trying to do all of these calculations in one statement. I can get them working fine separately but can't get the formatting right to combine them. Any help would be really appreciated.

1 Answer 1

2

Assuming you have in A1 (week 1, 39h) and in A2 (week 2, 45h).

Your total overtime (5h) will be in cell A3 with this formula:

=IF(A1>40;A1-40;0)+IF(A2>40;A2-40;0)
1
  • Perfect, thank you. That's exactly how I was trying to simplify the statement :)
    – Matt
    Commented Dec 28, 2015 at 12:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.