Count Weekday (Mon,Sun,Sat) in given date range

[sohailcdc]

I am looking for Access VBA code to count number of Day Name in given range

e.g. March 01, 2014 to March 31, 2014
Mon = 5
Tue = 4
Wed = 4
Thu = 4
Fri = 4
Sat = 5
Sun = 5
Thanks in advance

 

[Frothingslosh]

Use the Weekday function: http://www.techonthenet.com/access/functions/date/weekday.php

 

[sohailcdc]

Thanks, but I am looking for range, whereas per Weekday syntax only provide info for specific date

 

[jdraw]

What exactly do you want reported?
You may find this useful.

 

[Brianwarnock]

Thanks, but I am looking for range, whereas per Weekday syntax only provide info for specific date

You use the weekday function in a loop to find the day number ( of the week) for each date in the range, this is then used to update a 7 element array.

Myarray(daynum-1)=Myarray(daynum-1) + 1

Element 0 is day 1 as arrays are 0 based.

If the range is likely to be large it may be more efficient to initially divide the number of days by 7 and update each element of the array to that before then using the loop to add the extra days as defined by the remainder of the division, start at the start date for that number of days.

I cannot code this for you as I no longer have Access.

Brian

 

[Frothingslosh]

To be fair, you can set the array to range from 1-7 - arrays merely default to a lower bound of zero; it's not an actual requirement.

Dim aDayCount(1 to 7) as Integer

is perfectly valid.

 

[Brianwarnock]

To be fair, you can set the array to range from 1-7 - arrays merely default to a lower bound of zero; it's not an actual requirement.

Dim aDayCount(1 to 7) as Integer

is perfectly valid.

Thanks, I thought so but could not remember how to so Googled and could not find an answer, however threads re creating arrays did not mention it so decided I might have dreamt it.
It is a long time since I did this.

Brian

 

[sohailcdc]

Thanks to all, but I basically design non-professional logic below is what I did

Private Sub butcal_Click()
On Error GoTo meform

Dim begdate As Variant
Dim enddate As Variant
Dim totaldays As Integer
Dim datecounter As Integer
Dim countsun As Integer
Dim countmon As Integer
Dim counttue As Integer
Dim countwed As Integer
Dim countthu As Integer
Dim countfri As Integer
Dim countsat As Integer

If IsNull(Me.txtfromdate) Or IsNull(Me.txttodate) = True Then
MsgBox "Please Insert From and To Date", vbOKOnly, "Erro"
Exit Sub
End If

begdate = DateValue(Me.txtfromdate)
enddate = DateValue(Me.txttodate)
totaldays = DateDiff("d", begdate, enddate)

Do While datecounter <= totaldays
If Format(begdate, "ddd") = "Sun" Then
countsun = countsun + 1
End If
If Format(begdate, "ddd") = "Mon" Then
countmon = countmon + 1
End If
If Format(begdate, "ddd") = "Tue" Then
counttue = counttue + 1
End If
If Format(begdate, "ddd") = "Wed" Then
countwed = countwed + 1
End If
If Format(begdate, "ddd") = "Thu" Then
countthu = countthu + 1
End If
If Format(begdate, "ddd") = "Fri" Then
countfri = countfri + 1
End If
If Format(begdate, "ddd") = "Sat" Then
countsat = countsat + 1
End If

datecounter = datecounter + 1
begdate = DateAdd("d", 1, begdate)

Loop


Me.txttotaldays = countmon + counttue + countwed + countthu + countfri + countsat + countsun

End sub

 

[Frothingslosh]

That should work, but it's rather a cumbersome way to do it.

Personally, I'd suggest something like this:

Private Sub butcal_Click()
 
Dim DateStart As Date
Dim DateEnd As Date
Dim DateCheck As Date
Dim aDayCount(1 To 7) As Long
Dim DayOfWeek As Long
 
    [COLOR=seagreen]'Pull values from the form.[/COLOR]
    DateStart = Me.txtfromdate
    DateEnd = Me.txttodate
    DateCheck = DateStart
 
   [COLOR=seagreen]'Loop through each date.[/COLOR]
    Do Until DateCheck > DateEnd
       [COLOR=seagreen]'Determine the day of the week upon which the current date falls.[/COLOR]
        DayOfWeek = Weekday(DateCheck)
        [COLOR=seagreen]'Increment the indicated element of the array aDayCount by 1.[/COLOR]
        aDayCount(DayOfWeek) = aDayCount(DayOfWeek) + 1
       [COLOR=seagreen]'Advance to the next date in the date range being checked.[/COLOR]
        DateCheck = DateCheck + 1
    Loop
 
   [COLOR=seagreen]'All days have been counted, so now insert the data in the form.[/COLOR]
    For DayOfWeek = 1 To 7
       [COLOR=seagreen]'Determine which weekday it is.[/COLOR]
        Select Case DayOfWeek
            Case 1  [COLOR=seagreen]'Sunday[/COLOR]
               [COLOR=seagreen]'Do whatever you need for each day here.  Example follows.[/COLOR]
[COLOR=seagreen]               'Update Me.txtSundayCount.[/COLOR]
                Me.txtSundayCount.Value = aDayCount(1)
            Case 2  [COLOR=seagreen]'Monday[/COLOR]
                Me.txtMondayCount.Value = aDayCount(2)
            Case 3  [COLOR=seagreen]'Tuesday[/COLOR]
                'Etc, etc etc
            Case 4  [COLOR=seagreen]'Wednesday[/COLOR]
            Case 5  [COLOR=seagreen]'Thursday[/COLOR]
            Case 6  [COLOR=seagreen]'Friday[/COLOR]
            Case 7  [COLOR=seagreen]'Saturday[/COLOR]
        End Select
    Next DayOfWeek
 
  [COLOR=seagreen]  'Update the value of Me.txttotaldays.[/COLOR]
    Me.txttotaldays.Value = DateDiff("d", DateStart, DateEnd) + 1
 
End Sub

One thing you should definitely do is start using either camel case (itLooksLikeThis) or mixed caps (ItLooksLikeThis) to make your variable and function names more legible.

 

[Brianwarnock]

Don't forget that the total number of days is
Datediff(......)+1

Brian

 

[namliam]

May be tooooooo simple but how about

Function countDays(StartDate As Date, endDate As Date, thisWeekday As Integer) As Integer
    dim StartMonday as date, EndSunday as date
    startMonday = StartDate + 8 - Weekday(StartDate, vbMonday)
    endSunday = endSunday - Weekday(endDate, vbMonday)
    countDays = Int((endDate - startMonday) / 7)
    If Weekday(StartDate, vbMonday) <= thisWeekday Then countDays = countDays + 1
    If Weekday(endDate, vbMonday) >= thisWeekday Then countDays = countDays + 1
End Function
Sub testit()
    Dim i As Integer
    For i = 1 To 7
    Debug.Print i, countDays(#3/1/2014#, #3/31/2014#, i)
    Next i
End Sub

Returns:

 1             5 
 2             4 
 3             4 
 4             4 
 5             4 
 6             5 
 7             5

 

[Frothingslosh]

Don't forget that the total number of days is
Datediff(......)+1

Brian

Crap, you're right. I even made a mental note about that when I was typing that up...and immediately forgot.