Help

Calculating intervals for repeating events

Topic Labels: Formulas
Solved
Jump to Solution
694 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Noah_Coleman
6 - Interface Innovator
6 - Interface Innovator

I have a base to keep track of tasks that need to be repeated every so often. I’ve been able to achieve this with the following formula, checking the modulus between today and the start date to see if it matches up with the repeat interval:
IF(MOD(DATETIME_DIFF(TODAY(),{Start Date},'days'),{Repeat Interval (days)})=0,"Today is the day!","Not yet...")
When the formula resolves to “Today is the day!”, an automation is triggered that creates an instance of the task on the to do list.
The limitation to this is I have to put my repeat interval in days. How would I go about building a formula that does the same thing, but could handle different interval inputs, like every 2 weeks or every 18 months?
I think I would need a number field and an interval field (e.g. 3 and "weeks"), then maybe a switch statement to handle the different cases?
If anyone has any examples or could point me towards a tutorial, I would be most grateful.

1 Solution

Accepted Solutions
Noah_Coleman
6 - Interface Innovator
6 - Interface Innovator

I was actually able to solve this using an automation and a few extra fields. The fields are a “repeat interval” number field, a “repeat unit” single select field, a “Last Run” calendar field, and a “Next Run” formula field: DATEADD({Last Run},{Repeat Interval},{Repeat Unit}). Then the check formula is a very simple IF({Next Run}=TODAY(),"", "🛑"). An automation is triggered when this field changes to “ :alarm_clock: ”, makes the to do list task and also changes the “last run” field to the current date. If I create or change a master task, I just need to make sure that the “next run” field is after today, or the formula will never trigger.

See Solution in Thread

1 Reply 1
Noah_Coleman
6 - Interface Innovator
6 - Interface Innovator

I was actually able to solve this using an automation and a few extra fields. The fields are a “repeat interval” number field, a “repeat unit” single select field, a “Last Run” calendar field, and a “Next Run” formula field: DATEADD({Last Run},{Repeat Interval},{Repeat Unit}). Then the check formula is a very simple IF({Next Run}=TODAY(),"", "🛑"). An automation is triggered when this field changes to “ :alarm_clock: ”, makes the to do list task and also changes the “last run” field to the current date. If I create or change a master task, I just need to make sure that the “next run” field is after today, or the formula will never trigger.