IMM - 3rd Wednesday of Month

[agmorgan]

In finance there is a trade type call IMM dates- "International Money Market" dates
Essentially this means that the trade repeats on the third wednesday of each month.
How can I calculate this, given a random start date, using Excel?

[Ecniv]

Have a look in the help files for date functions... specifically weekday.
Then you need to figure out if the first day of the month is before or after the wednesday to get its date then add on 21 (days).

[opus]

how about

Public Sub test()

If Weekday((Date - (Day(Date) - 1)), vbMonday) = 3 Then
'first day of month was wendsday
    ActiveSheet.Cells(2, 1) = Date - Day(Date) + 15
ElseIf Weekday((Date - (Day(Date) - 1)), vbMonday) < 3 Then
'first day of month was weekday before wendsday
    ActiveSheet.Cells(2, 1) = Date - Day(Date) + 15 + (3 - Weekday((Date - (Day(Date) - 1)), vbMonday))
Else
'first day of month was weekday after wendsday
    ActiveSheet.Cells(2, 1) = Date - Day(Date) + 15 + (10 - Weekday((Date - (Day(Date) - 1)), vbMonday))
End If
End Sub