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!
Solved
If statement with date formula
+2Best answer by TheTimeSavingCo
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.
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.
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?
+31Could 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?
+2Could 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?
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.
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.
+31Hi! 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.
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.
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?
+2Ahh 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?
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.
+31Hi 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.
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!
+2Glad 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!
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!
Login to the community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.