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.

Solution

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)

Comments

[Raystafarian]

check here and here or see if this works

[phuclv]

Possible duplicate of What Excel formula can I use to calculate the 2nd Monday of a given month?

[Raystafarian]

yup