Having issues with IF formula

[Dustin_Good]

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?

[Ben_Young1 · Accepted solution]

Hey @Dustin_Good!

Here’s a quick revision I made to your original formula:

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:

Dustin_Good:

What am I doing incorrectly?

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.

[Dustin_Good]

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.

[Angelena_Zeiser]

Hello. I need help please.
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.

[Ben_Young1]

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

[Angelena_Zeiser]

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