You probably haven’t received an answer to your question yet because this is a complicated calculation.
It is fairly straightforward to calculate the {3rd Friday of the current month}, because the 3rd Friday will always be a date between the 15th and 21st:
SWITCH(
WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({date}, "YYYY-MM") & "-15", 'YYYY-MM-DD')),
0, DATETIME_PARSE(DATETIME_FORMAT({date}, "YYYY-MM") & "-20", 'YYYY-MM-DD'),
1, DATETIME_PARSE(DATETIME_FORMAT({date}, "YYYY-MM") & "-19", 'YYYY-MM-DD'),
2, DATETIME_PARSE(DATETIME_FORMAT({date}, "YYYY-MM") & "-18", 'YYYY-MM-DD'),
3, DATETIME_PARSE(DATETIME_FORMAT({date}, "YYYY-MM") & "-17", 'YYYY-MM-DD'),
4, DATETIME_PARSE(DATETIME_FORMAT({date}, "YYYY-MM") & "-16", 'YYYY-MM-DD'),
5, DATETIME_PARSE(DATETIME_FORMAT({date}, "YYYY-MM") & "-15", 'YYYY-MM-DD'),
6, DATETIME_PARSE(DATETIME_FORMAT({date}, "YYYY-MM") & "-21", 'YYYY-MM-DD')
)
It is a bit trickier to calculate the next 3rd Friday, because it might be in the same month or the following month.
The {3rd Friday of the following month} can be calculated by calculating the following month with DATEADD({date}, 1, 'month') , and then inserting the result in the above formula.
SWITCH(
WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT(DATEADD({date}, 1, 'month'), "YYYY-MM") & "-15", 'YYYY-MM-DD')),
0, DATETIME_PARSE(DATETIME_FORMAT(DATEADD({date}, 1, 'month'), "YYYY-MM") & "-20", 'YYYY-MM-DD'),
1, DATETIME_PARSE(DATETIME_FORMAT(DATEADD({date}, 1, 'month'), "YYYY-MM") & "-19", 'YYYY-MM-DD'),
2, DATETIME_PARSE(DATETIME_FORMAT(DATEADD({date}, 1, 'month'), "YYYY-MM") & "-18", 'YYYY-MM-DD'),
3, DATETIME_PARSE(DATETIME_FORMAT(DATEADD({date}, 1, 'month'), "YYYY-MM") & "-17", 'YYYY-MM-DD'),
4, DATETIME_PARSE(DATETIME_FORMAT(DATEADD({date}, 1, 'month'), "YYYY-MM") & "-16", 'YYYY-MM-DD'),
5, DATETIME_PARSE(DATETIME_FORMAT(DATEADD({date}, 1, 'month'), "YYYY-MM") & "-15", 'YYYY-MM-DD'),
6, DATETIME_PARSE(DATETIME_FORMAT(DATEADD({date}, 1, 'month'), "YYYY-MM") & "-21", 'YYYY-MM-DD')
)
Now, how do you decide if the the next 3rd Friday is in the current month or the following month?
If the current date is before the 15, the 3rd Friday must be in the current month, and if the current date is after the 22, the 3rd Friday must be in the next month.
IF(
DAY({date}) <= 15,
{3rd Friday of the current month},
IF(
DAY({date}) >= 22,
{3rd Friday of the following month}
))
But what if the current date is between the 15th and the 22? The next 3rd Friday might be later that same month, or it might be the following month:
IF(
DAY({date}) <= 15,
{3rd Friday of the current month},
IF(
DAY({date}) >= 22,
{3rd Friday of the following month},
SWITCH(DAY({date}),
16, SWITCH(WEEKDAY({date}),
0, DATEADD({date}, 5, 'days'),
1, DATEADD({date}, 4, 'days'),
2, DATEADD({date}, 3, 'days'),
3, DATEADD({date}, 2, 'days'),
4, DATEADD({date}, 1, 'days'),
5, {date},
6, {3rd Friday of the following month}
),
17, SWITCH(WEEKDAY({date}),
0, {3rd Friday of the following month},
1, DATEADD({date}, 4, 'days'),
2, DATEADD({date}, 3, 'days'),
3, DATEADD({date}, 2, 'days'),
4, DATEADD({date}, 1, 'days'),
5, {date},
6, {3rd Friday of the following month}
),
18, SWITCH(WEEKDAY({date}),
0, {3rd Friday of the following month},
1, {3rd Friday of the following month},
2, DATEADD({date}, 3, 'days'),
3, DATEADD({date}, 2, 'days'),
4, DATEADD({date}, 1, 'days'),
5, {date},
6, {3rd Friday of the following month}
),
19, SWITCH(WEEKDAY({date}),
0, {3rd Friday of the following month},
1, {3rd Friday of the following month},
2, {3rd Friday of the following month},
3, DATEADD({date}, 2, 'days'),
4, DATEADD({date}, 1, 'days'),
5, {date},
6, {3rd Friday of the following month}
),
20, SWITCH(WEEKDAY({date}),
0, {3rd Friday of the following month},
1, {3rd Friday of the following month},
2, {3rd Friday of the following month},
3, {3rd Friday of the following month},
4, DATEADD({date}, 1, 'days'),
5, {date},
6, {3rd Friday of the following month}
),
21, SWITCH(WEEKDAY({date}),
0, {3rd Friday of the following month},
1, {3rd Friday of the following month},
2, {3rd Friday of the following month},
3, {3rd Friday of the following month},
4, {3rd Friday of the following month},
5, {date},
6, {3rd Friday of the following month}
)
)))
Upon re-reading your original post, I see that your original date is always a 3rd Friday, thus, your calculated 3rd Friday will always be in the following month, so you can skip several of the formulas that I provided and just use the formula for the {3rd Friday of the following month}.
