If statement with date formula

[dre_1]

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!

[TheTimeSavingCo]

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? 

[dre_1]

Hi! Thank you for replying.

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. 

So far I've only made: 

DATEADD(Date, 1, 'week') 
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. 

[TheTimeSavingCo · Accepted solution]

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?

[dre_1]

Hi there Adam! 
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.

[TheTimeSavingCo]

Glad I could help!

I think you were on the right track with the fields to see when each promo would end for the customer and that you would have gotten there given a bit more familiarity with the formula syntax

When I started out I also found the formulas pretty challenging but after practice it gets easier, so I think you'll do fine and be on your way to writing complex formulas pretty soon!

[dre_1]

I've written a reply to this and liked it, not sure as to how my reply never came through. Looking back into this as I needed more clarity in my future formulas.

Anyways, thank you again for helping me out on this, I'm still pretty slow in figuring out formulas. YouTube has helped a bit but the topics they're explaining are not even close to what I'm trying to do. Appreciate the help you've done for my specific problem, have a good day!