Can I alter this formula to generate a date based off a deadline of the 4th Friday of every month?

Emily Mc.

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!

Lucas Rayala

@Emily Mc. ultimately you could probably figure out how to do this formula, but I suggest cheating. Create a lookup table and manually enter the date for the fourth Friday of every month. You can enter several years worth of Fridays in five minutes. Use this lookup table as your basis for your formula.