I have a spreadsheet for a monthly card club that meets on the last Friday of every month. In the row header I have date of last Friday of the month, name of person who is hosting, address, phone number, and email. For this year I input the date manually. Is there a way to do this automatically when I make next years schedule?
Trying to make it easier for me and learn something along the way. I also have another card club that meets on the third Wednesday of each month. I sure I could use it there too.
—–
Roger Ramsey
You can do it with some pretty complex functions. For instance, given a date in cell C5 like "March 2022" you can use this in D5:
EOMONTH(C5,0)+MOD(−WEEKDAY(EOMONTH(C5,0))−1,−7)
This takes the last day of the month, then adds the -7 MOD of the WEEKDAY number for the same month, subtracting 1 for Friday (2 for Thursday, 3 for Wednesday, etc). This gives you the Friday before the date, or the date itself if it is a Friday.
You can create something similar for the 3rd Wednesday. It is actually a little easier. The first day of any month is just DATE(YEAR(C5),MONTH(C5),1).