MacMost Q&A Forum • View All Forum QuestionsAsk a Question

How Do I Output a Date When It Is a Certain Day Of the Month?

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

Comments: One Response to “How Do I Output a Date When It Is a Certain Day Of the Month?”

    2 months ago

    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).

Leave a New Comment Related to "How Do I Output a Date When It Is a Certain Day Of the Month?"

:
:
:
0/500 (500 character limit -- please state your comment succinctly and do not try to get around this limit by posting two comments)