Hi,
In California there are two way for hourly workers get over time.
1. Weekly hours > 40
2. Daily hours > 8
I've attached a file.
I need help getting the cleanest formula that shows total over time.
Here's my current idea to show a week's overtime:
IF (Weekly hours greater than 40, then take the difference, else show zero) BUT IF (any day's hours are greater than 8) then SUM ((IF Monday > 8 then take the difference, else show zero) (same for Tuesday) (and so on))
In other words:
IF (I6>40,I6-40,0) BUT IF (B6 or C6 or D6 or E6 or F6 or G6 or H6) then SUM(IF(B6>8,B6-8,0)IF(C6>8,C6-8,0)IF(D6>8,D6-8,0)IF(E6>8,E6-8,0)IF(F6>8,F6-8,0)IF(G6>8,G6-8,0)IF(H6>8,H6-8,0))
I don't think there is a "BUT IF" formula, but that's the idea general idea.
I want to use this in "Weekly OT by Person" tab, and "Total Weekly OT" cell.
Please help, thanks.
Excel 2016
In California there are two way for hourly workers get over time.
1. Weekly hours > 40
2. Daily hours > 8
I've attached a file.
I need help getting the cleanest formula that shows total over time.
Here's my current idea to show a week's overtime:
IF (Weekly hours greater than 40, then take the difference, else show zero) BUT IF (any day's hours are greater than 8) then SUM ((IF Monday > 8 then take the difference, else show zero) (same for Tuesday) (and so on))
In other words:
IF (I6>40,I6-40,0) BUT IF (B6 or C6 or D6 or E6 or F6 or G6 or H6) then SUM(IF(B6>8,B6-8,0)IF(C6>8,C6-8,0)IF(D6>8,D6-8,0)IF(E6>8,E6-8,0)IF(F6>8,F6-8,0)IF(G6>8,G6-8,0)IF(H6>8,H6-8,0))
I don't think there is a "BUT IF" formula, but that's the idea general idea.
I want to use this in "Weekly OT by Person" tab, and "Total Weekly OT" cell.
Please help, thanks.
Excel 2016