PDA

View Full Version : [SOLVED] How do I declare a module wide date that is in the form of say (3rd wednesday of the



anish201
07-19-2005, 05:51 PM
:dunno 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
07-19-2005, 06:39 PM
This Kb Entry (http://www.vbaexpress.com/kb/getarticle.php?kb_id=42) 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
07-20-2005, 03:11 AM
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