I have a column of consecutive dates that span multiple months. A second column list the day name for the date. How do I find every 3rd Friday of any month? I'd like to have it display some text in a 3rd column next to the particular Friday.
If the formula is generic enough, I'd like to find other types of days such as every 1st Friday, every 2nd Thursday, etc.
I'm assuming that by "every 3rd Friday of any month" you mean "the third Friday of each month," and that your meaning for "every second Tuesday" and "every first Friday" is similar.
The first occurrence of any particular weekday in a particular month will be on one of the first seven days in that month, the second occurrence in the second seven days, and the third occurrence in the third seven days.
The day number within a month is returned by the DAY(date) function.
First: DAY(date)>0 AND DAY(date)<8
Second: DAY(date)>7 AND DAY(date)<15
Third: DAY(date)>14 AND DAY(date)<22
Fourth: DAY(date)>21 AND DAY(date)<29
A number corresponding to the day of the week is returned by the WEEKDAY(date) function. By default, the week starts on Sunday, which is assigned the WEEKDAY value 1.
So for a date to be the third Friday of a month, three things must be TRUE:
WEEKDAY(date) must be 6
DAY(date) must be greater than 14
DAY(date) must be less than 22.
Expressed as a formula, placed in the same row as the date, and with the dates listed in column A,
=IF(AND(WEEKDAY($A)=6,DAY($A)>14,DAY($A)<22),"Third Friday","")
will place "Third Friday" beside the third Friday of each month, and a null string in the other rows.
There should be enough information there to write the formulas for the other cases.
Regards,
Barry
Excellent Barry. Thanks.
How to find every 3rd Friday?
