I've watched Mynda explain how to find the first (or last) Monday of a month, but I need a way to find the dates for the third Wednesday of each month so I can preset dates for the forthcoming 12 months for my Association to hold meetings. If fact is it possible to make it variable to find nth occurrence of a predetermined day of month for a variable number of months?
Hi Ted,
You can use the same formula, choose Wednesday from the data validation list to return the first Wednesday, and then add 14 days to take you to the third Wednesday.
=DATE(2023,{1;2;3;4;5;6;7;8;9;10;11;12},7)- WEEKDAY(DATE(2023,{1;2;3;4;5;6;7;8;9;10;11;12},1)-3,3)+14
Mynda
Thank you Mynda. I found this one as well (as if I ever needed more confusion in my life).
=LET(start,EOMONTH(TODAY(), -1) +1,months,B5,n,B11,dow,B8,end,EDATE(start,months)-1,dates,SEQUENCE(end-start+1,1,start,1),fdates,FILTER(dates,TEXT(dates,"dddd") =dow),instance,BYROW(fdates,LAMBDA(d,SUM((TEXT(d,"mmyy")=TEXT(fdates, "mmyy"))*(d>=fdates)))),FILTER(fdates,instance=n))
Just for the fun of it, here's another one. Inspired by the formula you posted but a bit easier, I think.
Riny, go easy on me please.....
Riny, your solution works well providing I don't go past 12 months. If I used a figure greater than 12 for the value in B5 then it starts to double up the date values in the results section. In fact if I use 27 then it triples up some date values
Correct! but then again, I went after your original question that mentioned "for the forthcoming 12 months".
That is true, but I also asked if it were possible to make it a variable number of months as well. All that aside, thank you for taking the time to assist me in this matter.
I was trolling through the forum and came across this question. I thought Mynda’s solution was clever. So did ChatGPT, “This is the clever part “ is their quote.
So, I asked ChatGPT what their solution was. To be accurate, I asked about using the WORKDAY.INTL function. I should add that ChatGPT did not get the placement of the “0” correct when using the SEQUENCE function.
I only submit this to confirm a statement Mynda made in one of her Python videos that you must keep trying in ChatGPT to get a workable answer.
=WORKDAY.INTL(DATE(2023,SEQUENCE(12),1), 2, "1101111")
This is the explanation that ChatGPT gave:
DATE(2023,SEQUENCE(12),1) generates the first day of each month in 2023.
WORKDAY.INTL(..., 2, "1101111") counts 2 workdays (Wednesdays) from the start of the month. Since only Wednesdays are considered "working" in our custom weekly string, this will give us the third Wednesday of the month.
You must make sure that the workweek is set to Monday to Friday.
interesting, Cedric! Thanks for sharing.
