How to create due dates based on various frequencies

[Sofia (Sofia Malik)]

I have a board that maintains a checklist for tasks due throughout the month with various frequencies. For example Task 1 is due daily. And Task 5 is due every 2nd business day of the month. Is there a formula or some automation that can calculate what business day these tasks would be due, for example Task 5 would be due 1/2/24, 2/2/24, 3/2/24, etc. I don’t mind having 12 due date columns so all months can be visibible on the board.

[GCavin (Gilles Cavin - Reinventing Formulas in monday.com)]

Hi Sofia,

You could do it with the 3rd-party app called the Advanced Formula Booster. It literally revolutionizes formulas in monday.com.

• it doesn’t use the formula column but instead cast the formula results to any type of columns
• it can update multiple columns (from multiple items) in one formula
• it uses its own syntax editor which accomodates up to 100 lines (=100 instructions)
• It enables the use of variables for calculating and storing values, which can be easily accessed later in the formula.
• etc.

Having said that, I’ll choose ‘Every Monday’ from the list of frequencies you mentioned in your post to demonstrate its capabilities.

The following can be obtained with 1 formula and 1 automation:

 
This is definitely an advanced use of the Advanced Formula Booster.
Here is the syntax of the formula first and the simulation below (to understand what goes on in the background).

Don’t worry if it seems complicated. Our in-app Support Center is always available to help you whenever you need assistance.

Here is the simulation run inside the Advanced Formula Booster where TODAY is in April 2024.

1. Get the first day of the current month.
2. Get the number of days in the current month.
3. Create a list of this month’s dates and store it in [Dates]. In the Advanced Formula Booster, lists are elements separated by ‘|’.
4. Generate a corresponding list of weekdays for these dates using the WEEKDAY function on [Dates], and store the results in [Days] (1 = Monday, 2 = Tuesday, etc.).
5. Filter [Days] with the FILTER function to find positions of Mondays (noted as ‘1’). The result, showing the positions of Mondays in the list (1st, 8th, 15th, 22nd, and 29th) is stored in [MondayPos].
6. Extract the Monday dates from [Dates] using the position numbers in [MondayPos] and store this subset in [Mondays].
7. Count the Mondays (totaling 5) and store this number in [MondayCount].
8. Create a numerical list from 1 to the count of Mondays.
9. Formulate a list of target columns (Due Date 1, Due Date 2, up to Due Date 5) to cast the Monday dates, and store these column names in [DueDateFields].
10. To update column values, the format used is {Column}=NewValue. Enclosing the list of five columns within {}, paired with the [Mondays] list, updates each column with the corresponding Monday date.

[Ozi (Oz Israel)]

Hi @Sofia ,

For each task you can pair a status with lables like “Every day”, "Every week"etc. Now you can set the following automation for example:

When dete has passed and only if status is every day, then set date to today and push date by one day.

And so on and so forth.

Hope this helps,
Oz