Convert days like 2nd Monday or 4th Thursday to a Date

[Guest]

We schedule repetitive monthly services by the week and day of the month. The
completion date is recorded as a date. I need to verify that the work was
completed on the scheduled week and day. Is there a way to convert days like
1st Monday or 2nd Thursday to a date?

Thanks in Advance

Paul McC

 

[Ken Snell [MVP]]

Here is a function that I have posted in the past for doing this:
-----

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

 

[MGFoster]

We schedule repetitive monthly services by the week and day of the month. The
completion date is recorded as a date. I need to verify that the work was
completed on the scheduled week and day. Is there a way to convert days like
1st Monday or 2nd Thursday to a date?

Was discussed in this group in Oct 2004. Here is a link to that thread:

http://groups-beta.google.com/group...oup:comp.databases.ms-access#040f54bdf141ab6c

 

[Guest]

Thanks for your help. I'm only a novice with Visual Basic but I think I can
study your code and make it work.

Thanks Again

Paul