I have been struggling with this one for a couple of weeks now. I've created an app to calculate the meeting dates of sequential meetings along with their deadlines. After some feedback, users found it would be more useful if the calculator calculated the dates backward beginning with entering a target date for the last meeting in the series and calculating the dates for the first meeting.
For the first version of this calculator, I found Tim Leung's blog very helpful with some tweaking for our needs.
Tim uses With statements to great variables and so that's what I've done.
I figured out 2 of the 3 meetings but for some reason, the last meeting (technically the first meeting) is giving me issues. It took me a bit to figure out the function for the 2nd meeting so I applied the same logic to this last one and it does not work.
How I'm calculating the meetings:
The last meeting I have to calculate is every Monday except for the fifth Monday in the month (having issues); this date is determined by the second meeting date but it cannot be within 2-5 days from the second meeting date's memo deadline.
the second meeting is on the first Tuesday of every month - solved
the first meeting is on the first and third Tuesday of every month - solved
So depending on the month of the year, the calculated date is within 2-3 days of my target date. This is what I have on the DefaultDate for the Last meeting DatePicker: (DOW is DayOftheWeek & N is the week number in the month)
In addition, I created a variable to determine how many days were in between the memo deadline for meeting 2 and the last meeting because if the memo deadline and meeting date are too close together, the date needs to be moved. I was getting a circular reference error which is why I created the variable. The variable is on the OnChange of the text boxes for the memo deadlines. The variable isn't working either because the formula in the meeting datepicker isn't moving the date is not <=7.
1. The calculated date of the last meeting is <=7 days of the second meeting memo deadline, then the date needs to be pushed back a week to the Monday before. The variable I created works to calculate the days but in the datepicker formula it doesn't seem to impact much.
2. The calculated date calculated for the last meeting does not calculate to be a Monday. It usually falls within 2-3 days of the target date.
I hope this information is helpful and someone can help me figure this out.