# Help me add a date condition to a nested IF formula

#1

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!

#2

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"
)
``````

#3

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

#4

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…?

#5

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!!

#6

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.

#7

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!