Number of Mondays in month

[Michael Noblet]

I have a % utilization calculation I need to do in a
report where the denominator is the number of hours in a
shift * number of rooms * number of days.

Yhe number of days needs to be the number of a specific
day for the month (IE: the number of mondays in april =4)

therefore for the 7-3 shift on monday for the month of
april the denominator would be (8*7*4).

Is there a way to get the number of mondays in a month
without having to create a table?

 

[Wayne Morgan]

This will accept and date during the month. You also pass the Weekday value
you are looking for (i.e. 1 for Sunday, 2 for Monday, 3 for Tuesday, etc, up
to 7)

Public Function NumberOfDays(dteInputDate As Date, intWeekday As Integer) As
Integer
Dim dteFirstOfDay As Date, dteLastDayOfMonth As Date, dteFirstDayOfMonth As
Date
Dim intCounter As Integer
dteFirstDayOfMonth = DateSerial(Year(dteInputDate), Month(dteInputDate), 1)
If intWeekday >= Weekday(dteFirstDayOfMonth) Then
dteFirstOfDay = dteFirstDayOfMonth - Weekday(dteFirstDayOfMonth) +
intWeekday
Else
dteFirstOfDay = dteFirstDayOfMonth - Weekday(dteFirstDayOfMonth) + 7 +
intWeekday
End If
dteLastDayOfMonth = DateSerial(Year(dteInputDate), Month(dteInputDate) + 1,
0)
Do While dteFirstOfDay <= dteLastDayOfMonth
intCounter = intCounter + 1
dteFirstOfDay = dteFirstOfDay + 7
Loop
NumberOfDays = intCounter
End Function

 

[Michael Noblet]

This looks like it will work. Di I then use this function
within the calculation on the report? Sorry for the dumb
question, but I can see the function will work, but I am
not sure how to use it from there.

Mike

 

[Wayne Morgan]

Yes, you would use it as you would any built-in function. Place this one is
a standard module (one created in the modules tab of the database window).
If you don't already have a module there, when prompted to name the module,
don't give it a name of anything else in the database or the name of any
procedures.

You would then call the function in a calculated textbox in the report,
passing the date and day of the week.

Example:
=NumberOfDays(#7/15/2004#, 2)

or

=NumberOfDays([DateField], 2)