Skip to main content

Hi all, wondering if anyone can help me out here.


I am tracking some data which is time-sensitive and set up a trigger following Airtable’s recommended steps but I would like to customize it further by having the reminders triggered within 30 days before the due date.


For example - if the item is due on 1 May, I would like the reminder to be triggered in April.


How could I modify the formula?


For reference, this is the existing formula as recommended by Airtable:


IF(

AND(

{Due Date},

NOW() >= {Due Date}

),

“Trigger Reminder”

)


Thank you! Any help would be greatly appreciated.

MODIFY FORMULA to read

NOW()>= {Due Date}-14

for 2 weeks earlier


MODIFY FORMULA to read

NOW()>= {Due Date}-14

for 2 weeks earlier


Thank you so much!! This is great, works perfect!


MODIFY FORMULA to read

NOW()>= {Due Date}-14

for 2 weeks earlier



Welcome to the community, @Ron_Clark! :grinning_face_with_big_eyes: Unfortunately subtracting a number from a date as you indicated doesn’t produce a valid date.




The correct way to modify dates is by using the DATEADD() function. The original formula altered to use this would be:


IF(
AND(
{Due Date},
NOW() >= DATEADD({Due Date}, -14, "days")
),
"Trigger Reminder"
)

Unfortunately this will still produce an error if the {Due Date} field is empty.



You’ll need to check for a date first, then do the date comparison only if that date exists.


IF(
{Due Date},
IF(
NOW() >= DATEADD({Due Date}, -14, "days"),
"Trigger Reminder"
)
)



Welcome to the community, @Ron_Clark! :grinning_face_with_big_eyes: Unfortunately subtracting a number from a date as you indicated doesn’t produce a valid date.




The correct way to modify dates is by using the DATEADD() function. The original formula altered to use this would be:


IF(
AND(
{Due Date},
NOW() >= DATEADD({Due Date}, -14, "days")
),
"Trigger Reminder"
)

Unfortunately this will still produce an error if the {Due Date} field is empty.



You’ll need to check for a date first, then do the date comparison only if that date exists.


IF(
{Due Date},
IF(
NOW() >= DATEADD({Due Date}, -14, "days"),
"Trigger Reminder"
)
)


Thanks Justin! I found this really insightful. I took Ron’s original suggestion and used it with Date Add so it worked great for me.


I like what you showed and plan to use it!


Reply