# If statement with date formula

Topic Labels: Formulas
Solved
1836 6
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

Hi there, Im new to airtable and Im having difficulty with formulas in general.

Currently on my table is Name, Date and the Promo

What Im trying to achieve is that every time a new person and the date is added, the person is given a promo based on the date provided

For example
John Doe - Jan 1 2021 - promo 1 (during 1 week of the date)
John Doe - Jan 1 2021 - promo 2 (after 1 week but during 2 weeks of the date)
John Doe - Jan 1 2021 - promo 3 (after 2 weeks but during 3 weeks of the date)

I know this may be difficult which is why Im asking the community for assistance on this part.

Thank you!

1 Solution

Accepted Solutions
18 - Pluto

Ahh I see, is this sort of what you're looking for?

If so, here's a link to the base

And the script used is:

``````SWITCH(
DATETIME_DIFF(
TODAY(),
Date,
'weeks'
),
0, 'Promo 1',
1, 'Promo 2',
2, 'Promo 3'
)``````

The idea is to find out how many weeks it's been since the "Date" value, and based on that we'll know which promo they're on, does that make sense?

6 Replies 6
18 - Pluto

Could you provide specific examples of the values "Promo 1", "Promo 2", and "Promo 3"?  Do you want 3 promo dates to be created every time a new person and date is added?

5 - Automation Enthusiast

So, every new person (and date) that is added in Airtable is basically a customer who availed our products. The idea is that they have 1 week to avail our promo 1, and 2 weeks to avail our promo 2 and 3 weeks for promo 3 and lastly 4 weeks/1 month for our promo 4.

So, for example (from the pic) as soon as John Doe and the date he availed our product is added, he has a 7days/1 week to avail our promo 1 which makes "Current Promo" field as Promo 1. After a week, the field would now be as Promo 2 and after that week its Promo 3 and so on.

to show when the promo ends but that's not exactly what I'm hoping to achieve exactly as I'm trying to see what Current Promo John is on without having to check calendars and dates to confirm the duration of said promo. I know my request may seem difficult and I apologize for requiring a very specific solution.
18 - Pluto

Ahh I see, is this sort of what you're looking for?

If so, here's a link to the base

And the script used is:

``````SWITCH(
DATETIME_DIFF(
TODAY(),
Date,
'weeks'
),
0, 'Promo 1',
1, 'Promo 2',
2, 'Promo 3'
)``````

The idea is to find out how many weeks it's been since the "Date" value, and based on that we'll know which promo they're on, does that make sense?

5 - Automation Enthusiast

I can't believe it....it really works! Thank you so much, I appreciate it. I can't believe it looks so simple on your end and I was lost for days trying out all kinds of nested ifs/switch formulas.

No matter, I've deleted all my imperfect formulas for yours and it looks so much cleaner now. Thank you once again for your help and I apologize if my problem may have been confusing. Now I know I am not built for coding hahaha.

18 - Pluto