Push Due Date 45 Days from Next Calendar Quarter End

[MJ-CCO (Mary Johnston)]

Hello! I am new to Monday.com and I have certain government reports that I have to file X days after each quarter end, those days generally being 10, 45, 90, and 120 days.

For my first report, I have created a Group, and there are no other Groups on this board, called “File XYZ Report”. XYZ Report must be filed no later than 45 days after then end of each calendar quarter. Under this Group, I have a Task “Ensure XYZ Report is Filed”, and then various Subitems to be performed to create and file XYZ report. These The Subitem Due Dates are each X number of days before the Task Due Date. Currently, my board looks something like this:

(Group) “File XYZ Report”
(Task) “Ensure XYZ Report is Filed” with columns {Person}{Subitems}{Priority}{Status}{Due Date}{Last Updated}
(Subitem) “Pull data as of the previous quarter end” with columns {Owner}{Priority}{Status}{Last Updated}{Quarter End}{Next Due in # Days}

Note: For the {Quarter End} column, I am currently using the formula IF({Due Date},“Q”&FORMAT_DATE({Due Date},“Q-Y”),“”) and each cell under the {Next Due in # of Days} column has the number 45.

I am sure some of these columns are extraneous, but I added them to try and find a solution to my question, which is, once the Task is marked “Done”, how can I get the Task Due Date to update based upon the need to file the next report 45 days after the next quarter? Similarly, once each Subitem is marked Done, how can I get each Subitem Due Date to push to X number of days before the Task is due?

My first instance of “Ensure XYZ Report is Filed” has a Due Date of 02/14/2022 (45 days after 12/31/2021), and my Subitem Due Dates are February 1, February 5, and February 10. Once I complete each Subitem, I would like to reset those Due Dates to April 1, April 5, and April 10 (those dates don’t have to be precisely those days, I just need enough lead time to complete each Subitem). And once all Subitems are changed to Done, I’d like the Task Due Date to reset to 45 days after 03/31/2022, and so on for each subsequent quarter.

Hopefully, I have explained this series of events clearly enough, but I extend my apologies, if I haven’t. I appreciate your help and am happy to add any additional information you may find useful to assist in finding a solution. Thank you!

MJ

[SarahJacobs]

Hi MJ, I can’t help, but I want to set quarterly deadlines too so am interested in any responses you get
Sarah

[MJ-CCO (Mary Johnston)]

I may have come up with somewhat of a solution. Here are the columns I have:

The Quarter Ending column doesn’t have any real function, other than to visually remind me of the quarter each Item and Subitem relates to, at that time. It is a formula:
IF({Due Date},“Q”&FORMAT_DATE({Due Date},“Q-Y”),"").

I have also created a new Status called “Next Quarter End”.

These are the automations I created:

As I mark each Subitem as “Done”, it’s Subitem Status changes to “Next Quarter End” and the Due Date of the Subitem is pushed out by 90 days. When all Subitems have been marked as “Done” and have subsequently automatically changed to “Next Quarter End”, the Status is changed to “Next Quarter End” and it’s Due Date is pushed out 90 days.

The result of the automations is this:

The challenges I foresee with this automation is that, because calendar months have differing numbers of days, I will want to review the updated Due Dates to see if they conform to the schedule I need or if they need to be tweaked by a day or two.

MJ