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.

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)
  • Perfect, thank you. That's exactly how I was trying to simplify the statement :) – Matt Dec 28 '15 at 12:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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