how many workdays in each Month (1 Viewer)

[JayAndy]

Hi

Is there any way of finding out in a query or vba the workdays in each month between to dates.

so for example between 01/03/2016 - 10/04/2016 so it gives me a Figure of March has 22 and April has 7 in that time period.

i have a piece of VBA that tells me how many work days in total.

 

[arnelgp]

here put in a module:
on your query:
WorkDays: GetNetWorkDays3(StartDate, EndDate, "")

Public Function GetNetWorkDays3( _
          ByVal datDateFrom As Variant, _
            ByVal datDateTo As Variant, _
                ByVal strCountry As String, _
                    Optional ByVal booExcludeHolidays As Boolean) As Long
    Dim swp As Variant
    Dim i As Variant
    Dim lngDays As Long
    Dim strDateFrom As String
    Dim strDateTo As String
    Dim strFilter As String
    Dim strSQL As String
    Dim lngHolidays As Long
    Dim rs As DAO.Recordset
    Dim tempDate As Date
    
    ' Name of table with holidays.
    Const cstrTableHoliday    As String = "tbl_eDealHolidays"
    ' Name of date field in holiday table.
    Const cstrFieldHoliday    As String = "holDate"
    Const cstrCountryField    As String = "CountryCode"
    
    If IsNull(datDateFrom) Then datDateFrom = Date
    If IsNull(datDateTo) Then datDateTo = Date
    If datDateFrom > datDateTo Then
        swp = datDateFrom
        datDateFrom = datDateTo
        datDateTo = swp
    End If
    datDateFrom = CDate(Format(datDateFrom, "mm/dd/yyyy"))
    datDateTo = CDate(Format(datDateTo, "mm/dd/yyyy"))
    tempDate = datDateFrom
    While tempDate <= datDateTo
        If InStr("/Saturday/Sunday/", Format(tempDate, "dddd")) = 0 Then
            lngDays = lngDays + 1
        End If
        tempDate = DateAdd("d", 1, tempDate)
    Wend
    
  If booExcludeHolidays And lngDays > 0 Then
    strDateFrom = Format(datDateFrom, "mm/dd/yyyy")
    strDateTo = Format(datDateTo, "mm/dd/yyyy")
    strFilter = "[" & cstrCountryField & "] = " & Chr(34) & strCountry & Chr(34) & " "
    strFilter = strFilter & " And " & _
            "[" & cstrFieldHoliday & "] Between #" & strDateFrom & "# And #" & strDateTo & "#;"
    strSQL = "SELECT [" & cstrFieldHoliday & "] FROM [" & cstrTableHoliday & "] " & _
            "WHERE " & strFilter
    Set rs = DBEngine(0)(0).OpenRecordset(strSQL)
    With rs
        If Not (.BOF And .EOF) Then .MoveFirst
        While Not .EOF
            If InStr("/Saturday/Sunday/", Format(rs(0).value, "dddd")) = 0 Then
                lngHolidays = lngHolidays + 1
            End If
            .MoveNext
        Wend
        .Close
    End With
    Set rs = Nothing
  End If
    GetNetWorkDays3 = lngDays - lngHolidays
End Function

 

[JayAndy]

Thanks but that gives the total working days, which l have all ready got. I'm after the working days of each month for a date range

 

[Svencanz]

For that you would need a calendar in which statutory holidays are marked, and these differ from country to country. They may be available from Outlook or other products? Or you could just build your own, as a table.

 

[MarkK]

Write a loop that . . .
1) receives a start and end date
2) starts at your start date
3) finds the end of the month (or the end date - whichever comes first)
4) calls arnel's function with 2) and 3) above
a. and sum's that result
5) go to 1) using 3) + 1 as your start date, and the original end date
. . . so essentially calling arnel's function for every month in your range.

 

[JayAndy]

Write a loop that . . .
1) receives a start and end date
2) starts at your start date
3) finds the end of the month (or the end date - whichever comes first)
4) calls arnel's function with 2) and 3) above
a. and sum's that result
5) go to 1) using 3) + 1 as your start date, and the original end date
. . . so essentially calling arnel's function for every month in your range.

Thanks Mark that worked