Calculating # of Days in Month

[Guest]

Is there a way programmatically to count how many Mondays there are for a
given Month. For example, January 2005 has 5-Mondays, 4-Tuesdays, etc.

 

[Paul Overway]

Function follows (watch for any wrap)

Function CountDoWInMonth(ByVal DateArg As Date, _
Optional DayOfWeek As Integer = 0) As Integer

' Calculate the number of matching days in
' the specified month.

Dim dtmFirst As Date
Dim intCount As Integer
Dim intMonth As Integer

If (DayOfWeek < vbSunday Or DayOfWeek > vbSaturday) Then
' Caller must not have specified DOW, or it
' was an invalid number.
DayOfWeek = Weekday(DateArg)
End If
intMonth = Month(DateArg)

' Find the first day of the month
dtmFirst = DateSerial(Year(DateArg), intMonth, 1)

' Move dtmFirst forward until it hits the
' matching day number.
Do While Weekday(dtmFirst) <> DayOfWeek
dtmFirst = dtmFirst + 1
Loop

' Now, dtmFirst is sitting on the first day
' of the requested number in the month. Just count
' how many of that day type there are in the month.
intCount = 0
Do While Month(dtmFirst) = intMonth
intCount = intCount + 1
dtmFirst = dtmFirst + 7
Loop
CountDoWInMonth = intCount
End Function

 

[Guest]

This will do it:

Function Find_Days(dtmStartDate As Date, intWeekDay As Integer) As Integer
'dtmStartDate is the date to start using. It should be the 1st of the month
'intWeekDay is the day of the week to search for.
'To call it for January, 2005 and find all Mondays:
'=Find_Days(#1/1/2005#,vbMonday)

Dim intDayCount As Integer 'Collects the number of week days in the month
Dim intEnd As Integer 'Contains the month
'When it changes, we are done

intDayCount = 0
intEnd = Month(dtmStartDate) 'Need to know when we have finished with
the month
Do While Month(dtmStartDate) = intEnd
If Weekday(dtmStartDate) = intWeekDay Then
intDayCount = intDayCount + 1
End If
dtmStartDate = DateAdd("d", 1, dtmStartDate)
Loop
Find_Days = intDayCount
End Function

 

[Richard Perry via AccessMonster.com]

Sash

If you would like the solution in the form of a single formula, it is as
follows -


Function NoofDaysInMonth(ByVal dteFirstOfMonth As Date, ByVal intDayOfWeek
As Integer) As Integer
'
' dteFirstOfMonth = date of the first day of the month
' intDayOfweek is the vbConstant for the day of the week (Sunday = 1)
'

NoofDaysInMonth = Abs(Abs(Weekday(dteFirstOfMonth) - ((18 - (Day(DateAdd
("d", -1, DateAdd("m", 1, dteFirstOfMonth))) / 2)) + intDayOfWeek)) >= (18 -
(Day(DateAdd("d", -1, DateAdd("m", 1, dteFirstOfMonth))) / 2))) + Abs(Abs
(Weekday(dteFirstOfMonth) - ((18 - (Day(DateAdd("d", -1, DateAdd("m", 1,
dteFirstOfMonth))) / 2)) + intDayOfWeek)) <= ((Day(DateAdd("d", -1, DateAdd
("m", 1, dteFirstOfMonth))) / 2) - 11)) + 3

End Function

 

[Rob Oldfield]

Yikes.

You feel like debugging that in six months time?

 

[Graham R Seach]

Not exhaustively tested, but it seems to work:

Public Function CountOfDayInMonth( _
dte As Date, iDay As VbDayOfWeek) As Integer
'Calculates the number of times the specified
'day of the week occurs within the specified
'month.
'
'dte is specified as a date because February
'has an extra day in leap years.
'
'INPUTS:
' dte = a date in the month/year in question.
' iDay = the day of the week you to count.
'
'RETURN VALUE:
' Integer value.
'
Dim dteFirst As Date
Dim dteLast As Date

'Get the date of the first specified day in the month.
dteFirst = DateSerial(Year(dte), Month(dte), 0) + _
(8 - Weekday(DateSerial(Year(dte), Month(dte), 0), iDay))

'Get the date of the last specified day in the month.
dteLast = DateSerial(Year(dte), Month(dte) + 1, 0) - _
(Weekday(DateSerial(Year(dte), Month(dte) + 1, 0), iDay) - 1)

'The rest is easy.
CountOfDayInMonth = Int((Day(dteLast) - Day(dteFirst)) / 7 + 1)
End Function


Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Guest]

LOL - My thoughts exactly - or If I have to go behind him tomorrow!

 

[Guest]

Interesting how many ways there are to get to the same place. I think my
solution is the most straightforward and easy to understand. If I were
expecting user input, I would do more editing on the date; however, it
appears this will be called programmatically and therefore should expect the
programmer to call the function properly. If it were necessary to determine
the first day of the month for the date entered, the following line of code
could be added as the first line of executable code:

dtmStartDate = DateAdd("d", -Day(dtmStartDate) + 1, dtmStartDate)

 

[Rob Oldfield]

One slight improvement (well, I think so anyway) added....

Not exhaustively tested, but it seems to work:

Public Function CountOfDayInMonth( _
dte As Date, iDay As VbDayOfWeek) As Integer
'Calculates the number of times the specified
'day of the week occurs within the specified
'month.
'
'dte is specified as a date because February
'has an extra day in leap years.
'
'INPUTS:
' dte = a date in the month/year in question.
' iDay = the day of the week you to count.
'
'RETURN VALUE:
' Integer value.
'
Dim dteFirst As Date
Dim dteLast As Date

'Get the date of the first specified day in the month.
dteFirst = DateSerial(Year(dte), Month(dte), 0) + _
(8 - Weekday(DateSerial(Year(dte), Month(dte), 0), iDay))

if month(dateadd("ww",5,dteFirst))=month(dteFirst) then
countofdayinmonth=5
else
countofdayinmonth=4
endif