Help

Formula for recurring date or next date based on "week of month"

Topic Labels: Automations Formulas
Solved
Jump to Solution
672 2
cancel
Showing results for 
Search instead for 
Did you mean: 
littleflamingo
5 - Automation Enthusiast
5 - Automation Enthusiast

I'm new-ish to AirTable formulas (though experienced with spreadsheet formulas) and I have a unique situation where I would like to calculate a calendar date based on a combination of units that include Frequency (eg, monthly or weekly), Day of Week (Mon, Tue, etc.), and Week# of Month.

For example:  X event occurs every three months on the third Weds of that month. These schedules are variable by record (it's a client service schedule), so some clients are serviced once per week while others are serviced once every other month, etc. 

The goal is to show at least the next service date after today (and if possible, all future service dates throughout the year) on a calendar view. But I cannot just add a number of days or weeks to calculate the next date because the schedule needs to place service events on the particular week/day/month combo.

I do not have a starting formula yet because I'm not sure what logic is needed to pull together the variables. If I can provide additional info or screenshots, I am happy to. I appreciate any assistance or guidance!

1 Solution

Accepted Solutions
Devon_Godfrey
6 - Interface Innovator
6 - Interface Innovator

I've..sort have done this?

I've built a task management system for myself and I have recurring tasks in it. This is my fields and formulas.

Single Select Field: Recurring? 
Options are as follows:
Screenshot from 2024-07-11 14-55-26.png

Formula Field: Next Recurring Date
The Due date field is a simple date field. 
Screenshot from 2024-07-11 14-56-06.png

Number Field: Frequency
This is a simple number field that feeds into the date add formula how many days, weeks, months, whatever.
Screenshot from 2024-07-11 14-57-59.png

Lastly, I have an automation that checks if a task is set to recurring when marked as complete, and if so it sets the due date to the formula date for recurring tasks. 
Screenshot from 2024-07-11 14-59-08.png

Screenshot from 2024-07-11 14-59-42.png

 

Hopefully this is a good nudge in the right direction. 

See Solution in Thread

2 Replies 2
Devon_Godfrey
6 - Interface Innovator
6 - Interface Innovator

I've..sort have done this?

I've built a task management system for myself and I have recurring tasks in it. This is my fields and formulas.

Single Select Field: Recurring? 
Options are as follows:
Screenshot from 2024-07-11 14-55-26.png

Formula Field: Next Recurring Date
The Due date field is a simple date field. 
Screenshot from 2024-07-11 14-56-06.png

Number Field: Frequency
This is a simple number field that feeds into the date add formula how many days, weeks, months, whatever.
Screenshot from 2024-07-11 14-57-59.png

Lastly, I have an automation that checks if a task is set to recurring when marked as complete, and if so it sets the due date to the formula date for recurring tasks. 
Screenshot from 2024-07-11 14-59-08.png

Screenshot from 2024-07-11 14-59-42.png

 

Hopefully this is a good nudge in the right direction. 

@Devon_Godfrey Thank you so kindly for your quick response and simple solution!! It worked marvelously.

To make the calculated date fall on the correct date, I kept it simple and will just ensure that the input date falls on the day of the week that I want the calculated date to fall. As long as the input date falls on the same day of the week that the scheduled future date is meant to be, this works. 

Now, I'm going to noodle through how to populate more than one upcoming date so that a monthly or quarterly projection can be generated. 

once again, thank you!