As a form submits answers to my Smartsheet, the submission date is recorded under a column titled "Created." This information is submitted to a committee meeting on the 3rd Wednesday of every month, so I needed this formula to specify at which meeting date each response row will be considered.
I found the formula below on this forum to give me the 3rd Wednesday of the next month based on a date in another column (titled "Created").
=IF(Created@row <= DATE(YEAR(Created@row), MONTH(Created@row), 1 + (7 * 3)) - WEEKDAY(DATE(YEAR(Created@row), MONTH(Created@row), 8 - 4)), DATE(YEAR(Created@row), MONTH(Created@row), 1 + (7 * 3)) - WEEKDAY(DATE(YEAR(Created@row), MONTH(Created@row), 8 - 4)), DATE(YEAR(Created@row) + IF(MONTH(Created@row) = 12, 1, 0), IF(MONTH(Created@row) = 12, 1, MONTH(Created@row) + 1), 1 + (7 * 3)) - WEEKDAY(DATE(YEAR(Created@row) + IF(MONTH(Created@row) = 12, 1, 0), IF(MONTH(Created@row) = 12, 1, MONTH(Created@row) + 1), 8 - 4)))
The problem is that this formula generates the next meeting date if the "Created" date comes before it at all.
For example: a new response was created yesterday (10/12/23) so the meeting date was automatically set by this formula to the next 3rd Wednesday upcoming (10/18/23). However, our submission deadline was actually the 4th Friday of last month (9/29/23) so the meeting date should be pushed to the following meeting in November (11/15/23).
I would like to set a deadline cutoff date of the 4th Friday of every month, pushing any dates after that to the following meeting.
For example: the correctly generated meeting date for anything after 9/29/23 (4th Friday) would be the 3rd Wednesday of November (11/15/23). Anything after 10/27/23 (4th Friday) would generate a meeting date of 12/20/23 (3rd Wednesday), and etc.
Any help would be greatly appreciated!
