Count Weekdays Between 2 Dates

[eckert1961]

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

 

[MSAccessRookie]

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

 

[eckert1961]

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

 

[MSAccessRookie]

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.

 

[eckert1961]

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

 

[MSAccessRookie]

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.