Reputation: 3
If it's the weekend, I want to calculate overtime hours only with .5hr break calculated in, and regular hours when its not the weekend. It works perfectly fine until i leave my "time in" and "time out" slots blank, then it automatically calculates the .5 in the first IF statement as a -.5 under overtime hours instead of leaving it at 0.00. Is there anyway around this?
=IF(WEEKDAY($A19,2)>5,(d19-c19)*24-**.5**,IF((D19-c19)*24>8,(d19-c19)*24-8.5,0
p.s. first time posting here, sorry if all this is messed up. Thanks for any help.
Upvotes: 0
Views: 107
Reputation: 1497
It's difficult to follow what you're asking, but here's my attempt to solve it based on some guesses about what you're saying:
Assumptions:
Step 1: Make sure you have hours to even consider.
= IF((D19-C19) > 0,<do something here>,0)
Step 2: Do something different on weekdays v. weekends.
= IF((D19-C19) > 0, IF(WEEKDAY($A19,2)>5, <calculate weekend>, <calculate weekday>), 0)
Step 3: Calculate weekends, but never allow a negative number. (Assumption: If a person works 15 minutes overtime, that is no overtime at all.)
= IF( (D19-C19) > 0,
IF(WEEKDAY($A19,2)>5,
MAX((D19-C19)*24-0.5, 0), <calculate weekday>), 0)
Step 4: Calculate weekdays, but never allow a negative number. (Assumption: If a person works 15 minutes overtime, that is no overtime at all.)
= IF( (D19-C19) > 0,
IF(WEEKDAY($A19,2)>5,
MAX((D19-C19)*24-0.5, 0),
MAX((D19-C19)*24-8.5, 0)
),
0)
Step 5: Optimize - Since it looks like the MAX function handles the case when D19-C19 is 0, we don't need the outer IF statement
= IF(WEEKDAY($A19,2)>5,
MAX((D19-C19)*24-0.5, 0),
MAX((D19-C19)*24-8.5, 0)
)
...and I think that gives you what you want.
Upvotes: 3