Help me add a date condition to a nested IF formula

[Beth_D]

Hi there. I’m having trouble creating a nested IF formula with a date parameter.

I use airtable to calculate invoice totals for my business, and we have been offering a 10% discount on orders of 4 or more items. I use a field with this function:

IF(Quantity1+Quantity2+Quantity3+Quantity4+Quantity5 > 3, “------>”, “NO”)

and a BILLING TOTAL formula field outputs different numbers based on the response.

The problem I’m having now is that we’ve increased the threshold to 6 items before we offer the discount. I would like to create a nested IF formula that can take into account the date a deal is closed. I don’t want to change the formula to > 5, because then it will calculate our past deals inaccurately.

How can I add a condition that will apply the discount if > 3 only if {Date closed} is before 6/24/18, and > 5 if {Date closed} is either blank or after 6/24/18?

I know this is super specific to my database, but I hope someone can help me! Thanks in advance!

[Jeremy_Oglesby]

I think this should do it:

IF(
  OR(
    AND(
      IS_BEFORE(
        {Date closed},
        '6/24/2018'
      ),
      Quantity1+Quantity2+Quantity3+Quantity4+Quantity5>3
    ),
    AND(
      OR(
        {Date closed} = BLANK(),
        IS_AFTER(
          {Date closed},
          '6/24/2018'
        )
      ),
      Quantity1+Quantity2+Quantity3+Quantity4+Quantity5>5
    )
  ),
  "------>",
  "NO"
)

[Beth_D]

Hmm. That’s seems like the right direction, but unfortunately it returns #ERROR!

[Jeremy_Oglesby]

Is it giving you #ERROR! in all situations?

I just tried it and I only get #ERROR! if {Date Closed} is blank:

I can work on that, but if you are getting #ERROR! in other situations as well, there may be something else going on in your base that I am not aware of…?

[Beth_D]

You’re right, it only shows an error on deals with no {Date closed}. I’d appreciate your help with this last issue, too. Thanks!!

[Jeremy_Oglesby]

Ok, had to rearrange a bit to get the BLANK error to go away - use this:

IF(
  {Date closed} = BLANK(),
  IF(
    Quantity1+Quantity2+Quantity3+Quantity4+Quantity5>5,
    "------>",
    "NO"
  ),
  IF(
    IS_AFTER(
      {Date closed},
      '6/23/2018'
    ),
    IF(
      Quantity1+Quantity2+Quantity3+Quantity4+Quantity5>5,
      "------>",
      "NO"
    ),
    IF(
      Quantity1+Quantity2+Quantity3+Quantity4+Quantity5>3,
      "------>",
      "NO"
    )
  )
)

NOTE: I changed the IS_AFTER date to 6/23/2018 so that 6/24 is included in the new >5 requirement.

[Beth_D]

YES! Jeremy, you’re my hero. I’m pretty proud of the database I’ve designed, but occasionally I’m reminded just how little I actually know, haha. Thanks again!