Date Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is anyone aware of a procedure that will calculate the first, second, third,
fourth and last weekday of any given month (e.g. the third Thursday of March
or the last Monday of August)? Thanks in advance.
 
A function that I've written previously:

Public Function DateForFixedDayOfMonthYear(Day_Year As Integer, _
Day_Month As Integer, Day_Day As Integer, Day_WeekNum As Integer) As
Date
' Day_Year is the actual year for the desired date
' Day_Month is the actual month for the desired date
' Day_Day is the number of the weekday (1 = Sunday, etc. to 7 = Saturday)
' Day_WeekNum is the ordinal value for the desired date (e.g., 1 = first, 2
= second)
'
' So, if you want the 2nd Wednesday of May 2005:
' MyDate = DateForFixedDayOfMonthYear(2005, 5, 4, 2)

DateForFixedDayOfMonthYear = DateSerial(Day_Year, Day_Month, _
8 - DatePart("w", DateSerial(Day_Year, Day_Month, 1), _
1 + Day_Day Mod 7) + (Day_WeekNum - 1) * 7)

End Function
 
Hi Doug,

This should get you started, but it needs thorough testing.


Function FirstXxxdayOfMonth( _
ByRef FirstOfMonth As Date, _
DayWanted As VbDayOfWeek, _
Optional N As Long = 1) As Date

'Returns the Nth Sunday/Monday/etc in the
'month starting FirstOfMonth.

Dim StartingMonth As Long

If (N < 1) Or (N > 5) Then 'impossible
Err.Raise 9, , Error(9) & " (weeks in month)."
End If

StartingMonth = Month(FirstOfMonth)
'Work out the first Xxxday
Do Until Weekday(FirstOfMonth, vbSunday) = DayWanted
FirstOfMonth = DateAdd("D", 1, FirstOfMonth)
Loop

If N > 1 Then
FirstOfMonth = DateAdd("ww", N - 1, FirstOfMonth)
End If

If Month(FirstOfMonth) <> StartingMonth Then
'We have overflowed into a different month
Err.Raise 6, , "Overflow into another month."
End If
FirstXxxdayOfMonth = FirstOfMonth

End Function
 
Ken said:
A function that I've written previously:

Public Function DateForFixedDayOfMonthYear(Day_Year As Integer, _
Day_Month As Integer, Day_Day As Integer, Day_WeekNum As Integer) As
Date
' Day_Year is the actual year for the desired date
' Day_Month is the actual month for the desired date
' Day_Day is the number of the weekday (1 = Sunday, etc. to 7 = Saturday)
' Day_WeekNum is the ordinal value for the desired date (e.g., 1 = first, 2
= second)
'
' So, if you want the 2nd Wednesday of May 2005:
' MyDate = DateForFixedDayOfMonthYear(2005, 5, 4, 2)

DateForFixedDayOfMonthYear = DateSerial(Day_Year, Day_Month, _
8 - DatePart("w", DateSerial(Day_Year, Day_Month, 1), _
1 + Day_Day Mod 7) + (Day_WeekNum - 1) * 7)

End Function
Click to expand...


Ken, I don't think I understand the need for the Day_Day
argument. My similar function seems to work too:

Public Function NthDayX(yr As Integer, mth As Integer, _
wk As Integer, daynum As Integer)
Dim fm As Date
fm = DateSerial(Year(Date), mth, 1)
NthDayX = DateSerial(Year(fm), mth, _
8 - DatePart("w", fm - 1, daynum) + 7 * (wk - 1))
End Function

Am I missing something?
 
Marshall Barton said:
Ken, I don't think I understand the need for the Day_Day
argument. My similar function seems to work too:

Public Function NthDayX(yr As Integer, mth As Integer, _
wk As Integer, daynum As Integer)
Dim fm As Date
fm = DateSerial(Year(Date), mth, 1)
NthDayX = DateSerial(Year(fm), mth, _
8 - DatePart("w", fm - 1, daynum) + 7 * (wk - 1))
End Function

Am I missing something?
Click to expand...

Marsh:

Day_Day would be something like Saturday.

Day_WeekNum would be which Saturday (1st, 2nd, and so on).

For instance, I believe your Thanksgiving is the 4th Thursday in November,
so to see when it is this year, you'd use

DateForFixedDay(2006, 11, vbThursday, 4)

I've got a similar function (only I also allow things like "2nd last
Wednesday of the month") in my June, 2005 "Access Answers" column at
http://www.accessmvp.com/djsteele/SmartAccess.html
 
Marshall Barton said:
Ken, I don't think I understand the need for the Day_Day
argument. My similar function seems to work too:

Public Function NthDayX(yr As Integer, mth As Integer, _
wk As Integer, daynum As Integer)
Dim fm As Date
fm = DateSerial(Year(Date), mth, 1)
NthDayX = DateSerial(Year(fm), mth, _
8 - DatePart("w", fm - 1, daynum) + 7 * (wk - 1))
End Function

Am I missing something?
Click to expand...

< g > What Doug said....
 
Ken said:
< g > What Doug said....
Click to expand...


Sheesh, I must have been really tired. I could have sworn
there was two Day numbers. Our two functions are
equivalent, you just use longer names than I do ;-)
 
Back
Top