Excel: Third Wednesday of a month

Question

I need to get the next IMM date after a date.

IMM dates are defined as the third Wednesday of every March/June/September/December.

Answer 1

If you have a date in A2 and want to get the next IMM date (based on your definition of IMM dates as the third Wednesdays of March, June, September and December) then use this formula

=LOOKUP(A2,DATE(YEAR(A2),{0,3,6,9,12;3,6,9,12,15},22)-WEEKDAY(DATE(YEAR(A2),{0,3,6,9,12;3,6,9,12,15},4)))

format result cell in required date format. If A2 is on an IMM date the formula gives you the next one, is that how it should work?

Explanation:

You can use this formula to get the first Wednesday in a month

=DATE(y,m,8)-WEEKDAY(DATE(y,m,4))

for any year = y and month = m you choose

The 4 can be varied to give different days of the week and the 8 can be changed to 22 to get the 3rd Wednesday of the month. In the above formula this concept is extended to produce a "table" of dates within the formula whereby the top "row" consists of the 3rd Weds of last December then the current year's Mar, Jun, Sep and Dec - row 2 has the current year's 4 dates followed by next March's. LOOKUP then looks up A2 in that table, matching with the last 3rd wed in the top row and returning the next 3rd wed from the bottom.

There is a shorter method which doesn't use WEEKDAY function but uses FLOOR......but I usually avoid that method because it relies on use of one specific date system (1900 or 1904) whereas the version I provided above works whichever date system you are using.

This version works with [the default] 1900 date system only

=LOOKUP(A2,FLOOR(DATE(YEAR(A2),{0,3,6,9,12;3,6,9,12,15},17),7)+4)

Answer 2

You could just do it the easy way. To find the third Wednesday of the first month of this year.

Where A1 is the first day of the year or DATE(YEAR(TODAY()),1,1):

=A1+(25-WEEKDAY(A1))