Search code examples
google-sheetsexcel-formulaformula

Total hours of overtime

I keep a google spreadsheet where I enter hours worked per day. I'm supposed to work 8 hours per day and anything more than that is overtime.

So I need to check if a value in the column with hours is greater than 8. If so I want the difference between 8 and the value entered, added to or accumulated in another cell so that I can see how many hours of overtime I have worked. Also if the value is less than 8 then I need to make a deduction.

Can anyone help me with such a formula?

Solution

  • Let's say your hours are entered in column B, and start on row 2. In a column further along, put the following formula:

    =if(B2>8,B2-8,0)

    This first checks whether that day have any overtime =if(B2>8 - if it does, it calculates how much B2-8 - if it doesn't, it shows 0 instead.

    Then, underneath that column, you can sum for your answer. It will look something like this.

    A         | B     | C
Day       | Hours | Overtime 
------------------------------
Monday    |  8    |  =if(B2>8,B2-8,0)
Tuesday   |  9    |  =if(B3>8,B3-8,0)
Wednesday |  9    |  =if(B4>8,B4-8,0)
Thursday  |  8    |  =if(B5>8,B5-8,0)
Friday    |  10   |  =if(B6>8,B6-8,0)
------------------------------
Total     | Total |  =SUM(C2:C6)