Hello,

I'm using the following code to calculate the number of Monday's and Wednesday's in a selected month.

Public Function GetWeekDays(pmoyr As String, pdays As String) As Integer
'************************************************
'Purpose: Compute number of weekdays
' in the specified mm/yyyy
' where 1 = Sunday thru 7 = Saturday
'Coded by: raskew
'Inputs: 1) ? GetWeekdays("02/2008", "2") 'count Mondays
' 2) ? GetWkdays("07/2008", "246") 'count Mondays, Wednesdays, Fridays

'Output: 1) 5
' 2) 13
'************************************************

Dim dteStart As Date
Dim dteEnd As Date
Dim intEnd As Integer
Dim intStart As Integer
Dim i As Integer

dteStart = DateValue(pmoyr)
dteEnd = DateAdd("m", 1, dteStart) - 1

For i = 1 To Len(pdays)
intStart = intStart + IIf(Weekday(dteStart) <= Int(Mid(pdays, i, 1)), 1, 0)
Next i

For i = 1 To Len(pdays)
intEnd = intEnd + IIf(Int(Mid(pdays, i, 1)) <= Weekday(dteEnd), 1, 0)
Next i

GetWeekDays = intStart + Len(pdays) * (DateDiff("ww", dteStart, dteEnd) - 1) + intEnd

End Function
This works great but I would like to modify it so that along with what it already does I have the option of calculating the number of Monday's and Wednesday's between 2 dates.

Any assistance would be greatly appreciated.

Thanks and regards,
Chris

Hello,

I'm using the following code to calculate the number of Monday's and Wednesday's in a selected month.

Public Function GetWeekDays(pmoyr As String, pdays As String) As Integer
'************************************************
'Purpose: Compute number of weekdays
' in the specified mm/yyyy
' where 1 = Sunday thru 7 = Saturday
'Coded by: raskew
'Inputs: 1) ? GetWeekdays("02/2008", "2") 'count Mondays
' 2) ? GetWkdays("07/2008", "246") 'count Mondays, Wednesdays, Fridays

'Output: 1) 5
' 2) 13
'************************************************

Dim dteStart As Date
Dim dteEnd As Date
Dim intEnd As Integer
Dim intStart As Integer
Dim i As Integer

dteStart = DateValue(pmoyr)
dteEnd = DateAdd("m", 1, dteStart) - 1

For i = 1 To Len(pdays)
intStart = intStart + IIf(Weekday(dteStart) <= Int(Mid(pdays, i, 1)), 1, 0)
Next i

For i = 1 To Len(pdays)
intEnd = intEnd + IIf(Int(Mid(pdays, i, 1)) <= Weekday(dteEnd), 1, 0)
Next i

GetWeekDays = intStart + Len(pdays) * (DateDiff("ww", dteStart, dteEnd) - 1) + intEnd

End Function
This works great but I would like to modify it so that along with what it already does I have the option of calculating the number of Monday's and Wednesday's between 2 dates.

Any assistance would be greatly appreciated.

Thanks and regards,
Chris

Believe it or not, you already have your answer.

Your code already finds the target dates between a starting date (dteStart = DateValue(pmoyr)) and an ending date dteEnd = (DateAdd("m", 1, dteStart) - 1).

All you should need to do is change the Function to pass a starting and ending date as a parameter instead of calculating them.
Public Function GetWeekDays(dteStart As Date, dteEnd As Date, pdays As String) As Integer
'************************************************
'Purpose: Compute number of weekdays
' between two supplied dates
' where 1 = Sunday thru 7 = Saturday
'Based on code supplied by: raskew
'Inputs: 1) ? GetWeekdays("02/2008", "2") 'count Mondays
' 2) ? GetWkdays("07/2008", "246") 'count Mondays, Wednesdays, Fridays

'Output: 1) 5
' 2) 13
'************************************************

' no longer needed - changed to a parameter
'
' Dim dteStart As Date
' Dim dteEnd As Date

Dim intEnd As Integer
Dim intStart As Integer
Dim i As Integer

' no longer needed - changed to a parameter
'
' dteStart = DateValue(pmoyr)
' dteEnd = DateAdd("m", 1, dteStart) - 1

For i = 1 To Len(pdays)
intStart = intStart + IIf(Weekday(dteStart) <= Int(Mid(pdays, i, 1)), 1, 0)
Next i

For i = 1 To Len(pdays)
intEnd = intEnd + IIf(Int(Mid(pdays, i, 1)) <= Weekday(dteEnd), 1, 0)
Next i

GetWeekDays = intStart + Len(pdays) * (DateDiff("ww", dteStart, dteEnd) - 1) + intEnd

End Function

Thanks for the response but unfortunately I need more detailed information.

All you should need to do is change the Function to pass a starting and ending date as a parameter instead of calculating them.

How would I go about changing the Function?

Regards,
Chris

Thanks for the response but unfortunately I need more detailed information.



How would I go about changing the Function?

Regards,
Chris

I am not sure what you mean. The revised Function was included in my previous post.

This time I read your 1st reply with both eyes open and actually saw what you did. To test the revised function I entered the following into the immediate window and got the desired result.

? GetDays ("01-Jan-2009","31-Mar-2009","46")
25

Thanks so much.

Chris

This time I read your 1st reply with both eyes open and actually saw what you did. To test the revised function I entered the following into the immediate window and got the desired result.

? GetDays ("01-Jan-2009","31-Mar-2009","46")
25

Thanks so much.

Chris


The pleasure was all mine. Glad that you were able to resolve your issue, and apologies for any confusion that my earlier post presented.