#Excel: Creating a List of Option Expiration Dates and Triple Witching Dates with Excel Formulas by David Hager

Those people that closely follow the workings of the U.S. financial market know that options expiration day is important. In particular, triple-witching refers to the quarterly expiration of index futures, index future options and certain stock options on the third Friday of March, June, September and December. The other months having a 3^rd Friday are also important option expiration dates.

=TODAY() ‘in cell A1

=INDEX(A1+ROW($1:$40),MATCH(1,(DAY(A1+ROW($1:$40))>14)*(DAY(A1+ROW($1:$40))<22)*(WEEKDAY(A1+ROW($1:$40))=5),0))+1 ‘formula in A2 and fill down.

This will afford the list of option expiration dates. In order to see triple witching option expiration dates, this conditional formatting formula must be used on the date list.

Triple=NOT(MOD(MONTH(A2),3)) ‘defined when active cell is A2.

For the first cell, a different conditional formatting formula is needed. With A1 as the active cell, define Start:

=OR(A1=INDEX(A1+ROW($1:$40),MATCH(1,(DAY(A1+ROW($1:$40))>14)*(DAY(A1+ROW($1:$40))<22)*(

WEEKDAY(A1+ROW($1:$40),11)=5),0))-28, A1=INDEX(A1+ROW($1:$40),MATCH(1,(DAY(A1+ROW

($1:$40))>14)*(DAY(A1+ROW($1:$40))<22)*(WEEKDAY(A1+ROW($1:$40),11)=5),0))-35)

When the conditional format is applied to cell A1, the characters will appear as bold purple in the model when that date is an options expiration date. In order to see triple witching option expiration dates in A1, this additional conditional formatting formula was used to hightlight bold red text if A1 contains a triple witching date.

=AND(Triple,Start)

You can download the file here.

OptionExpire