How do I declare a module wide date that is in the form of say (3rd wednesday of the

[anish201]

How do I declare a module wide date that is in the form of say (3rd wednesday of the month every Mar, June, Sep, Dec). This should be going forward to any number of years.
I want help with some code or ideas as to how I can write code which tells Excel that when I wrtie a date say Mar 23 or July 6 to check that, that date that is in the form of say (3rd wednesday of the month every Mar, June, Sep, Dec).
Like I want this code to list a column of sales, but if the sales fall after the 3rd wednesday of Mar, June, Sep or Dec I want Excel to do something else. For eg: if today is the second wednesday of mar then excel doesnt do anything but if it is the 3rd thursday then i want it to check for that date and see that it has passed the 3rd wed of march and hence now it should act in a certain way or produce a certain result.

[Jacob Hilderbrand]

This Kb Entry has an example of how to find the date for a certain occurance of a certain day.

Public Function FloatingHoliday(xYear As Integer, xMonth As Integer, xDay As Integer,  _ 
    xNumber As Integer) 
FloatingHoliday = DateSerial(xYear, xMonth, (8 - Weekday(DateSerial(xYear, xMonth, 1),  _ 
    (xDay + 1) Mod 8)) + ((xNumber - 1) * 7)) 
End Function

So to get the date of the third wednesday of a certain month you would do this.

MyDate = floatingholiday(2005,7,4,3)

2005 = Year
7 = Month
4 = Wednesday (Sunday = 1, Monday = 2)
3 = Occurance

[Bob Phillips]

Here is a riutine that can check whether a date is a valid instance, say the 1st Monday, 2nd Thursday, etc, and if the month is a quarter month

Function DateInstance(pzDate, pzDow As Long, pzInstance As Long, _
                      Optional pzQuarter As Boolean = False) As Boolean
Dim dteCheck As Date
Dim fValid As Boolean
fValid = DateSerial(Year(pzDate), Month(pzDate), 1 + 7 * pzInstance) - _
                Weekday(DateSerial(Year(pzDate), Month(pzDate), 8 - pzDow)) = _
             pzDate
If fValid Then
        If pzQuarter Then
            fValid = Month(pzDate) Mod 3 = 0
        End If
    End If
DateInstance = fValid
End Function

You would use it like

?DateInstance(DateSerial(2005,07,20),4,3)

will check if 20-Jul is the 3rd Wed (day number 4) of the month

?DateInstance(DateSerial(2005,06,20),4,2,True)

will check if 20-Jun is the 4th Tue (day number 2) of the month, and the month is a quarter month