Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jun 01, 2022 02:41 PM
Hey everyone!
I had help with a formula not too long ago, and I’ve simply tweaked it to apply to other tables… but as of now, I’m not getting the results I need.
Here’s the formula:
IF(
{Date},
IF(
Date > TODAY(),
IF(
DATETIME_DIFF(Date, TODAY(), "days") <= 30,
"30 days", "1 month",
IF(
DATETIME_DIFF({Date}, TODAY(), "days") <= 180,
"180 days",
"6 months"
)
),
"Send 6 month status check email"
),
"Send 30 day status check email"
)
Every status is showing as “Send 6 month status check email” even if it’s within 30-days. Help?
And can anyone offer resources for the clueless when it comes to building formulas (outside of the Airtable cheat sheet)? I would definitely appreciate the help.
Solved! Go to Solution.
Jun 02, 2022 09:36 PM
Thanks for the details! I’ve pasted a formula below that you can try, and here’s a screenshot of it in action:
Let me know if you need any changes!
IF(
AND(
DATETIME_DIFF(TODAY(), {Date}, "days") >= 30,
DATETIME_DIFF(TODAY(), {Date}, "days") <= 179),
"Send 30 day status check email",
IF(
AND(
DATETIME_DIFF(TODAY(), {Date}, "days") >= 180,
DATETIME_DIFF(TODAY(), {Date}, "days") <= 364),
"Send 6 month status check email",
IF(
DATETIME_DIFF(TODAY(), {Date}, "days") >= 365,
"Send 1 year status check email"
)
)
)
Jun 01, 2022 08:51 PM
Hey Tender, what do you want the formula to output here? I can’t really make sense of your formula and would rather just write a new one for you.
Jun 02, 2022 07:45 AM
Hey Adam!
Bit of background, non-profit that provides emergency bill pay support… We want to follow-up with those that have made specific financial requests in 30 day, 180 day and 12 month intervals. I want the status to update based on the original request date to trigger email automation.
Jun 02, 2022 09:36 PM
Thanks for the details! I’ve pasted a formula below that you can try, and here’s a screenshot of it in action:
Let me know if you need any changes!
IF(
AND(
DATETIME_DIFF(TODAY(), {Date}, "days") >= 30,
DATETIME_DIFF(TODAY(), {Date}, "days") <= 179),
"Send 30 day status check email",
IF(
AND(
DATETIME_DIFF(TODAY(), {Date}, "days") >= 180,
DATETIME_DIFF(TODAY(), {Date}, "days") <= 364),
"Send 6 month status check email",
IF(
DATETIME_DIFF(TODAY(), {Date}, "days") >= 365,
"Send 1 year status check email"
)
)
)
Jun 03, 2022 12:21 PM
This works perfectly and is exactly what I needed! You’re amazing, and I appreciate your help so much!!! :raised_hands: