How ot determine the specific weekday of the month...

[Jim Wood]

I'm sure this is simple, but I haven't been able to figure out how to
identify the second Sunday of the current month. I need to know if today (or
the most recent Sunday if today is not Sunday) is the second Sunday of the
month. I am trying to include this data on a form that my vba snippit reads
to determine whether to display a certain field or not.

If someone could point me in the right direction I would be grateful.

Thank you,

Jim Wood

 

[PC Datasheet]

Date() gives you the current date.

Format([MyDateField],"dddd") gives you the day of the week for MyDateField

Format(Date(),"dddd") tells you what weekday the current date is.

 

[Ken Snell [MVP]]

Here is a function that I use to get the specific date for a particular date
in a month:


Function GetActualDateForGenericDayOfMonth(lngYear As Long, _
lngMonthNumber As Long, lngWeekDayNumber As Long, _
lngWeekDayOccurrenceOfMonth As Long) As Date
' Ken Snell - July 1, 2004
'***THIS FUNCTION RETURNS THE ACTUAL DATE FOR A GENERIC
'***DAY OF A MONTH (e.g., THE THIRD MONDAY OF A MONTH).
'***THE VARIABLES PROVIDED TO THE FUNCTION:
'*** lngYear - the year for the date.
'*** lngMonthNumber - the number of the month for the date
'*** (e.g., January = 1, February = 2, etc.).
'*** lngWeekDayNumber - the number of the weekday for the date
'*** (e.g., Sunday = 1, Monday = 2, etc.).
'*** lngWeekDayOccurrenceOfMonth - the order number of the weekday for
the date
'*** (e.g., 1 = first occurrence of that weekday in the month,
'*** 2 = second occurrence of that weekday in the month, etc.).
'***IF AN ERROR OCCURS DURING THE EVALUATION OF THE DATE, A VALUE OF ZERO
'***IS RETURNED BY THE FUNCTION.

On Error Resume Next
GetActualDateForGenericDayOfMonth = DateSerial(lngYear, lngMonthNumber, _
8 - DatePart("w", DateSerial(lngYear, lngMonthNumber, 1), _
1 + lngWeekDayNumber Mod 7) + (lngWeekDayOccurrenceOfMonth - 1) * 7)
If Err.Number <> 0 Then GetActualDateForGenericDayOfMonth = 0
Err.Clear
End Function



So, to thet the date of the second Sunday of March 2005:

?GetActualDateForGenericDayOfMonth(2005, 3, 1, 2)
3/13/2005


If you always want to use the current month and year, then

?GetActualDateForGenericDayOfMonth(Year(Date()), Month(Date()), 1, 2)
3/13/2005

 

[Jim Wood]

Thanks Ken, that looks like I might be able to tweak that a bit for my
particular use. Appreciate the help.

Jim