• C++ Programming for Financial Engineering
    Highly recommended by thousands of MFE students. Covers essential C++ topics with applications to financial engineering. Learn more Join!
    Python for Finance with Intro to Data Science
    Gain practical understanding of Python to read, understand, and write professional Python code for your first day on the job. Learn more Join!
    An Intuition-Based Options Primer for FE
    Ideal for entry level positions interviews and graduate studies, specializing in options trading arbitrage and options valuation models. Learn more Join!

3rd Fridays in an array - Excel

Joined
12/1/07
Messages
22
Points
11
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!
 
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.
 
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.)
 
Back
Top