Q: My amateur association keeps a diary of future events. Some of these are booked as, say, "Saturday in week 23 of each year" or "Second Thursday in May of each year". For a particular year, how do I convert these into dd/mm/yy? -Alan


A: You're going to have to approach each of these on a case-by-case situation, and it's probably going to involve some programming with loops. Microsoft Outlook handles dates like this.

For example, to find the 2nd Monday of January 2008, you'd have to set up a loop like this:

' NumMondays = 0
' d = #1/1/2008#
' While NumMondays < 2
' If Weekday(d) = 2 Then '2=Monday
' NumMondays = NumMondays + 1
' End If
' d = d + 1
' Wend
' d = d - 1
' MsgBox d

When you're done, you should have your 2nd Monday. Of course, you can manipulate the dates to search for any month, or even build this into a function so you can send it a month and year, and all that jazz... but this should get you started.

Subscribe to Access: Calculate 2nd Monday in January
Get notifications when this page is updated