Calculate the 3rd Friday of the month

  • Thread starter Thread starter StacyM
  • Start date Start date
S

StacyM

I have a form that is based on a table. I would like a field on that form to
show the date of the 3rd Friday of a given month and year is. The month is
also a seperate field as well as the year. This seems easy, but I don't know
what the expression or VBA code would look like. Thanks for your help.
 
From an old posting:

Public Function fDayInMonth(WeekNumber As Integer, Wkday As Integer, _
dMonth As Integer, dYear As Integer) As Date

Dim FirstOfMonth As Date
Dim NextWeekDay As Integer
Dim RootDate As Date

FirstOfMonth = DateSerial(dYear, dMonth, 1)
NextWeekDay = IIf(Wkday = vbSaturday, vbSunday, Wkday + 1)
RootDate = FirstOfMonth - Weekday(FirstOfMonth, NextWeekDay)
fDayInMonth = RootDate + WeekNumber * 7

End Function

To get the date of the 3rd thursday in september 1965 , use
DayInMonth(3, vbThursday, 9, 1965)
Did this help?

Regards,
Wolfgang

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Thanks for your help. I am not sure it is quite what I am looking for
though. I need to be able to calculate dates in the future. The month and
year are both combo boxes on the form. The month field is a text field and
the year is a number field. I am also very new to VBA so simple is best!
Thanks again
 
Change your combo box so that it has two columns: the month number and the
month description. You don't have to show the month number: you just need to
ensure that it's the bound column so that referring to the combo box returns
the month number.

Let's assume that you've got the months in cboMonth and the years in
cboYear. Assuming you're calling the function in VBA code, you'd use the
following (regardless of whether it's a future or historic date):

DayInMonth(3, vbThursday, Me.cboMonth, Me.cboYear)
 
Works nicely, thanks for the help!

Douglas J. Steele said:
Change your combo box so that it has two columns: the month number and the
month description. You don't have to show the month number: you just need to
ensure that it's the bound column so that referring to the combo box returns
the month number.

Let's assume that you've got the months in cboMonth and the years in
cboYear. Assuming you're calling the function in VBA code, you'd use the
following (regardless of whether it's a future or historic date):

DayInMonth(3, vbThursday, Me.cboMonth, Me.cboYear)
Click to expand...
 
Back
Top