David Benford
David Benford

Reputation: 3

Nested IF statements, can't return "0" because 2nd IF statement is in the way

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

Answers (1)

StoneGiant
StoneGiant

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:

  • Column A is the Day.
  • Column C is Time In.
  • Column D is Time Out.
  • Weekend hours are always overtime.
  • A 30 minute break is deducted from overtime hours on any given day.
  • You are attempting to calculate the total of overtime hours per day.

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

Related Questions