Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Re: Formula Help for Dates

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

1
1852
0

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

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

Sep 22, 2021 09:54 AM

This is our current working formula.

IF(AND({Number of Items}=5, {VA Recipient?}),57.39, IF({Number of Items}=5, 55, IF(AND({Number of Items}=7, {VA Recipient?}),73.18, IF({Number of Items}=7, 70, IF(AND({Number of Items}=10, {VA Recipient?}),88.98, IF({Number of Items}=10, 85))))))

We want to specify dates for this. So, if before October 1, 2020, then the above formula is correct.

BUT between October 1, 2020 and October 1, 2021, the formula should be:

IF(AND({Number of Items}=5, {VA Recipient?}),57.70, IF({Number of Items}=5, 55, IF(AND({Number of Items}=7, {VA Recipient?}),73.60, IF({Number of Items}=7, 70, IF(AND({Number of Items}=10, {VA Recipient?}),89.50, IF({Number of Items}=10, 85))))))

And then finally, starting October 1, 2021, the formula should be:

IF(AND({Number of Items}=5, {VA Recipient?}),63, IF({Number of Items}=5, 60, IF(AND({Number of Items}=7, {VA Recipient?}),78.90, IF({Number of Items}=7, 75, IF(AND({Number of Items}=10, {VA Recipient?}),94.80, IF({Number of Items}=10, 90))))))

How do I use the IS_BEFORE and IS_AFTER formula correctly to account for this situation? It’s getting quite complicated as I try to do it…

2 Replies 2

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

Sep 22, 2021 05:20 PM

With some restructuring and swapping out IF()s for SWITCH()s, this isn’t too complex. For starters, your original formula can be simplified down to:

```
SWITCH(
{Number of Items},
5, IF({VA Recipient?}, 57.39, 55),
7, IF({VA Recipient?}, 73.18, 70),
10, IF({VA Recipient?}, 88.98, 85)
)
```

Since you have three “major” scenarios to look at (your date ranges), I created a nested IF() statement that outputs 1, 2, or 3 depending on where the value of the `{Date Field}`

falls. If the field is empty, no value is returned.

```
IF(
IS_BEFORE({Date Field}, "2020-10-01"),
1,
IF(
IS_BEFORE({Date Field}, "2021-10-01"),
2,
IF({Date Field}, 3)
)
)
```

That nested IF() can be wrapped inside a SWITCH() statement to make plugging in your other variable scenarios easier:

```
SWITCH(
IF(IS_BEFORE({Date Field}, "2020-10-01"), 1, IF(IS_BEFORE({Date Field}, "2021-10-01"), 2, IF({Date Field}, 3))),
1, "Formula for before 2020-10-01",
2, "Formula for between 2020-10-01 and 2021-10-01",
3, "Formula for 2021-10-01 onward"
)
```

So all together:

```
SWITCH(
IF(IS_BEFORE({Date Field}, "2020-10-01"), 1, IF(IS_BEFORE({Date Field}, "2021-10-01"), 2, IF({Date Field}, 3))),
1, SWITCH(
{Number of Items},
5, IF({VA Recipient?}, 57.39, 55),
7, IF({VA Recipient?}, 73.18, 70),
10, IF({VA Recipient?}, 88.98, 85)
),
2, SWITCH(
{Number of Items},
5, IF({VA Recipient?}, 57.70, 55),
7, IF({VA Recipient?}, 73.60, 70),
10, IF({VA Recipient?}, 89.50, 85)
),
3, SWITCH(
{Number of Items},
5, IF({VA Recipient?}, 63, 60),
7, IF({VA Recipient?}, 78.90, 75),
10, IF({VA Recipient?}, 94.80, 90)
)
)
```

Reply

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

Sep 24, 2021 01:05 PM

Wow. Super, super helpful. I had not used the Switch formula before.