Skip to main content

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!

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:

Formula Field: Next Recurring Date
The Due date field is a simple date field. 

Number Field: Frequency
This is a simple number field that feeds into the date add formula how many days, weeks, months, whatever.

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. 

 

Hopefully this is a good nudge in the right direction. 

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:

Formula Field: Next Recurring Date
The Due date field is a simple date field. 

Number Field: Frequency
This is a simple number field that feeds into the date add formula how many days, weeks, months, whatever.

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. 

 

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!

Reply


Terms & Conditions