The Airtable Community will undergo scheduled maintenance on September 17 from 10:00 PM PST to 11:15 PM PST. During this period, you may experience temporary disruptions. We apologize for any inconvenience and appreciate your understanding.

# Having issues with IF formula

Topic Labels: Formulas
Solved
1601 5
cancel
Showing results for
Did you mean:
6 - Interface Innovator

I’m building a credit card base where we store expiration dates. One formula will simply tell us that a card is set to expire (aka when status is “Expiring”) within the month. At this point I have an automation set to email client with expiring card message. Now I’m trying to set up a second automation triggered by a formula field to let me know what the card is now “Expired”.

So far this formula works to tell me if the card is Expiring or Valid.
IF({Expiration Date}<TODAY(),“Expiring”,“Valid”)

However now I want to add an “Expired” status too. Yet when I do this entire formula it doesn’t work.
IF({Expiration Date}<TODAY(),“Expiring”,“Valid”), (IF(DATETIME_DIFF(TODAY(),{Expiration Date},‘days’) > 30, ‘Expired’))

What am I doing incorrectly?

1 Solution

Accepted Solutions
11 - Venus

Hey @Dustin_Good!

IF(
{Expiration Date},
IF(
DATETIME_DIFF(
TODAY(),
{Expiration Date},
'days'
) > 30,
'Expired',
IF(
{Expiration Date} < TODAY(),
'Expiring',
'Valid'
)
)
)

This formula will return the following behavior:

I’ll give you a quick breakdown regarding this:

This is a formatted version of the formula you posted.

1 IF(
2   {Expiration Date} < TODAY(),
3   "Expiring",
4   "Valid"
5 ),
6 IF(
7   DATETIME_DIFF(
8       TODAY(),
9           {Expiration Date},
10          'days'
11  ) > 30,
12  "Expired"
13 )

The problem is found on line 5. It’s a comma syntax issue.
It seems intuitive to separate different functions using a comma, but an essential syntax rule is that you will never find commas outside of a function.

That formula is functionally: IF(a, b, c), IF(a, b, c).

Now, if you don’t want to nest a bunch of IF functions and have functions that won’t step on each, per se, you can use an ampersand in place of the comma to join them.

IF(a, b, c) & IF(a, b, c).

Here’s what that looks like:

IF(
{Expiration Date} < TODAY(),
"Expiring",
"Valid"
)
&
IF(
DATETIME_DIFF(
TODAY(),
{Expiration Date},
'days'
) > 30,
"Expired"
)

So, the ampersand joins them, but as you can see, one of the formulas returned two IF functions as true and evaluated both parameters, producing the ExpiringExpired string.

I was referring to this when I said that you want to ensure that your functions don’t step on each other’s toes.

To prevent this, you’d either nest the second IF function or change the evaluation parameter in your first IF function.

It can be confusing, but once you write out or rubber duck what you’re trying to do, it becomes much easier to think about nesting.
This makes it easier for me to identify strange syntax issues.
But to each their own.

5 Replies 5
11 - Venus

Hey @Dustin_Good!

IF(
{Expiration Date},
IF(
DATETIME_DIFF(
TODAY(),
{Expiration Date},
'days'
) > 30,
'Expired',
IF(
{Expiration Date} < TODAY(),
'Expiring',
'Valid'
)
)
)

This formula will return the following behavior:

I’ll give you a quick breakdown regarding this:

This is a formatted version of the formula you posted.

1 IF(
2   {Expiration Date} < TODAY(),
3   "Expiring",
4   "Valid"
5 ),
6 IF(
7   DATETIME_DIFF(
8       TODAY(),
9           {Expiration Date},
10          'days'
11  ) > 30,
12  "Expired"
13 )

The problem is found on line 5. It’s a comma syntax issue.
It seems intuitive to separate different functions using a comma, but an essential syntax rule is that you will never find commas outside of a function.

That formula is functionally: IF(a, b, c), IF(a, b, c).

Now, if you don’t want to nest a bunch of IF functions and have functions that won’t step on each, per se, you can use an ampersand in place of the comma to join them.

IF(a, b, c) & IF(a, b, c).

Here’s what that looks like:

IF(
{Expiration Date} < TODAY(),
"Expiring",
"Valid"
)
&
IF(
DATETIME_DIFF(
TODAY(),
{Expiration Date},
'days'
) > 30,
"Expired"
)

So, the ampersand joins them, but as you can see, one of the formulas returned two IF functions as true and evaluated both parameters, producing the ExpiringExpired string.

I was referring to this when I said that you want to ensure that your functions don’t step on each other’s toes.

To prevent this, you’d either nest the second IF function or change the evaluation parameter in your first IF function.

It can be confusing, but once you write out or rubber duck what you’re trying to do, it becomes much easier to think about nesting.
This makes it easier for me to identify strange syntax issues.
But to each their own.

6 - Interface Innovator

Wow…thank you for the thorough breakdown. It’s taking some time for me to get the hang of formulas but this kind of explanation is one that I will return to over and over.

6 - Interface Innovator

I currently have this:
IF({Will a partner or guess be attending the afternoon portion?} = “Yes”, “2”, “1”)
but want to add another IF statement:
IF ({RSVP} = “Will not be attending” then “0” or blank.

11 - Venus

Hey @Angelena_Zeiser!

This might be worth tossing into a new post altogether.
There might be some optimization we can do to fit your needs better, but for your formula question, here’s a quick fix:

IF(
NOT(
{RSVP} = "Will not be attending"
),
IF(
{Will a partner or guess be attending the afternoon portion?} = "Yes",
2,
1
)
)

Alternatively, you could use:

IF(
{RSVP} = "Will not be attending",
'',
IF(
{Will a partner or guess be attending the afternoon portion?} = "Yes",
2,
1
)
)
6 - Interface Innovator

Ben, thank you so much! This worked! I’m also not sure how to mark your reply with the checkmark. Sorry!