Google-sheets – Google Sheets overtime calculation if/and/or statements

google sheets

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.

Best Answer

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)
Related Topic