[AccessD] Count of a weekday between two dates

Hi Mark

At least not for negative counts. But you can test with a small test sub I've used:

Public Sub WeekdayDiffTest()

  Const datDate1  As Date = #4/5/2008#
  Const datDate2  As Date = #4/13/2008#
  
  Dim bytWeekday  As Byte
  
  Debug.Print WeekDay(datDate1), datDate1
  Debug.Print WeekDay(datDate2), datDate2, DateDiff("d", datDate1, datDate2)
  For bytWeekday = vbSunday To vbSaturday
    Debug.Print bytWeekday, WeekdayDiff(datDate1, datDate2, bytWeekday), DateDiff("ww", datDate1, datDate2, bytWeekday)
  Next
  
End Sub

/gustav

>>> markamatte at hotmail.com 17-04-2008 16:06 >>>

Would this approach work?

Thanks,

Mark A. Matte

***********START*******************
Dim StartDT As Date
Dim EndDT As Date
Dim CT_WeekDay
StartDT = #1/7/2008#
EndDT = #1/23/2008#
CT_WeekDay = 0

Do Until StartDT> EndDT
Dim tt
tt = DatePart("w", StartDT)
    If DatePart("w", StartDT)> 1 And DatePart("w", StartDT) < 7 Then
        CT_WeekDay = CT_WeekDay + 1
    End If
    StartDT = DateAdd("d", 1, StartDT)
Loop
MsgBox CT_WeekDay & " weekdays."
*************END*******************


> Date: Thu, 17 Apr 2008 15:37:13 +0200
> From: Gustav at cactus.dk 
> To: accessd at databaseadvisors.com 
> Subject: [AccessD] Count of a weekday between two dates
>
> Hi all
>
> Recently I had to find the count of a weekday between two dates. Much to my surprise such code was not to be located.
> I had an old function for this but found out, that the trick is to fool DateDiff to believe that the week starts on the weekday in question.
>
> 
> Public Function WeekdayDiff( _
> ByVal datDate1 As Date, _
> ByVal datDate2 As Date, _
> Optional ByVal bytWeekday As Byte) _
> As Long
>
> ' Returns signed count of a weekday of value bytWeekday
> ' between datDate1 and datDate2.
> '
> ' bytWeekday values should be one of the constants from vbMonday to vbSunday.
> ' If not or if not supplied, the weekday of datDate1 is assumed.
> '
> ' 2001-08-23. Cactus Data ApS, CPH.
> ' 2008-04-09. Rewrite using DateDiff.
>
> Dim lngWeekdayDiff As Long
>
> ' No special error handling.
> On Error Resume Next
>
> ' Validate bytWeekday.
> Select Case bytWeekday
> Case _
> vbMonday, _
> vbTuesday, _
> vbWednesday, _
> vbThursday, _
> vbFriday, _
> vbSaturday, _
> vbSunday
> Case Else
> ' None or invalid value for weekday.
> bytWeekday = WeekDay(datDate1, vbSunday)
> End Select
>
> lngWeekdayDiff = DateDiff("ww", datDate1, datDate2, bytWeekday)
>
> WeekdayDiff = lngWeekdayDiff
>
> End Function
> 
>
> Have fun!
>
> /gustav