3rd Fridays in an array - Excel

[Urszula]

Hi guys :sos:,

I have an array of dates starting in 2000 and I need to find which of them are 3rd Fridays of each month. Any ideas how to do it efficiently?

Thank you!

 

[Uncle Max]

First you should generate an array/column of 3d Fridays dates in the given range. You can use something like that

=(DATE(YEAR(...),MONTH(...),21)-WEEKDAY(DATE(YEAR(...),MONTH(...),16),1)+1)

Then, just use VLookup to get values from original data set matching Fridays you've found.

 

[Adam]

There's an easier way than creating a second range. The third Friday for any given month will have a Day value of 15 through 21. Not only that, any Friday with a Day value of < 15 or > 21 will be a first, second, fourth, or fifth Friday of a month. Therefore, you can check to see if a date is a 3rd Friday with...

=IF(WEEKDAY(A1)=6,AND(DAY(A1)>14,DAY(A1)<22))

(...assuming the date you're checking is in cell A1, of course.)

 

[Urszula]

Thank you!

Very helpful