Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Re: Having issues with IF formula

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Solved

Jump to Solution

0
1503
2

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 08, 2022 09:06 AM

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?

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 08, 2022 09:48 AM

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:

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.

Reply

5 Replies 5

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 08, 2022 09:48 AM

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:

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.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 08, 2022 10:08 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 08, 2022 06:50 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 08, 2022 08:04 PM

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

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 08, 2022 08:14 PM