Save the date! Join us on October 16 for our Product Ops launch event. Register here.
May 20, 2020 01:58 PM
I have a table where users submit events that happen on a weekly and monthly basis. For example: a yoga class every Saturday at 9am or a group run the first Sunday of every month. I don’t want to add a ton of records of that same event but would like the original date field (Event Date) to be updated so that it populates the next coming date.
So process would be:
Is there a formula that’ll auto-update that Event Date field? Or at least spit out that new date in a new formula field?
Solved! Go to Solution.
May 20, 2020 09:10 PM
If things happen on a varied reoccurring basis, you may consider a Single Select field called {Frequency}
with options for weekly
and monthly
, then you could have a Formula field with a formula like this:
IF(IS_AFTER({First Event Date},TODAY()), {First Event Date}, SWITCH(Frequency, "weekly", DATEADD({First Event Date}, DATETIME_DIFF(TODAY(), {First Event Date}, "weeks")+1, "weeks"), "monthly", DATEADD({First Event Date}, DATETIME_DIFF(TODAY(), {First Event Date}, "months")+1, "months")))
That formula will check if the first time the event is supposed to happen comes after the current date. If it is, the formula field will display the date entered in the form. Otherwise, the formula uses a SWITCH()
function to determine whether to add weeks or months to the first event date.
May 20, 2020 09:10 PM
If things happen on a varied reoccurring basis, you may consider a Single Select field called {Frequency}
with options for weekly
and monthly
, then you could have a Formula field with a formula like this:
IF(IS_AFTER({First Event Date},TODAY()), {First Event Date}, SWITCH(Frequency, "weekly", DATEADD({First Event Date}, DATETIME_DIFF(TODAY(), {First Event Date}, "weeks")+1, "weeks"), "monthly", DATEADD({First Event Date}, DATETIME_DIFF(TODAY(), {First Event Date}, "months")+1, "months")))
That formula will check if the first time the event is supposed to happen comes after the current date. If it is, the formula field will display the date entered in the form. Otherwise, the formula uses a SWITCH()
function to determine whether to add weeks or months to the first event date.
May 21, 2020 07:56 AM
Welcome to the community, @Erendira_Garcia! :grinning_face_with_big_eyes: To answer your question re: whether or not a formula could update the date, unfortunately the answer is no. Formula fields can only update themselves based on the data in other fields.
While the formula that @Kamille_Parks proposed would work for your “Saturday at 9am” situation, it doesn’t work for the “first Sunday of every month” option because adding a month to a given date will keep it on the same numerical day; e.g. May 3rd (the first Sunday) becomes June 3rd (the first Wednesday).
In my own planning base, I’ve created a system that lets me choose a variety of intervals:
This is all controlled using one multi-select field I call {Frequency}
. That’s the “easy” part. The rest is contained in 14 formula fields1 that carefully interpret the contents of {Frequency}
, finally spitting out a date in {Next Due}
based on the current date in {Due}
. Once a day (at 3am, I think), an Integromat scenario runs and copies the date from {Next Due}
into {Due}
for any record where {Due}
has just passed, so that each record only progresses to its next option when it’s scheduled to do so. I set the current due date and frequency for each record that needs such a schedule, and the rest is automatic.
Anyway, I share all that just to let you know that it’s possible to create a system that supports multiple types of intervals between events, but it’s not a quick setup. If you want more info, message me. I do plan on sharing more about this system at some point—very likely on my YouTube channel—but it’s much more complex than anything currently in my production schedule, making it harder to determine when/where it will fit.
Notes
May 21, 2020 11:51 AM
Thanks so much @Kamille_Parks and @Justin_Barrett these answers were just what I was looking for!
Kamille, your code worked perfectly! I was having such a hard time wrapping my brain around it so I truly appreciate your help. Maybe Airtable will base future recurring fields off that? :smirk:
Justin, you were right that it wouldn’t really work month-wise (definitely works for when you need the same date each month like paying a bill, but not when you need it to be based on day of the month). I appreciate you sharing your base break-down too, it might be easier to use an integration app to update the monthly dates rather than remember to do it myself.
Thank. you both!