Working Week

[Big_Si]

Hi All

Does anybody know a nice simple way of calculating the number of working days (monday to friday) between two dates in either a query or with some VB.

Currently I am exporting the data into Excel and using the "Networkdays" function but this is not ideal for my purposes.

Cheers

 

[raskew]

Hi -

You'll find numerous posts on business days/work days in a search and also in the code repository.

Here's one you can try. Note that it does not take into consideration holidays.

Function FindWorkDays(varDate1 As Variant, varDate2 As Variant, Optional incl As Boolean) As Double

'*******************************************
'Purpose:   returns number of workdays between two dates
'Coded by:  raskew
'Inputs:    from debug (immediate) window
'           ? findworkdays(#4/01/2006#, #02/26/2007#)
'Outputs:   236
'*******************************************
Dim dteDate1 As Date
Dim dteDate2 As Date
Dim fdays    As Integer
Dim fweeks   As Integer
Dim ldays    As Integer
Dim intdays  As Integer
Dim x        As Integer

    incl = IIf(IsMissing(incl), True, incl)
    
    If Not IsNull(varDate1) And Not IsNull(varDate2) Then
       If IsDate(varDate1) And IsDate(varDate2) Then
          dteDate1 = DateValue(varDate1)
          dteDate2 = DateValue(varDate2)
          intdays = DateDiff("d", dteDate1, dteDate2)
          x = WeekDay(dteDate1, 1)
          
          fdays = 7 - WeekDay(dteDate1, 1) '# of weekdays in starting week
          fweeks = 5 * Int((intdays - fdays) / 7) '# of calendar days in full weeks
          ldays = WeekDay(dteDate2, 1) - 1 '#of weekdays in ending week
          FindWorkDays = fdays + fweeks + ldays + IIf(incl, 0, IIf(IsWeekday(dteDate1), -1, 0))
       End If
    End If

End Function

HTH - Bob

 

[Big_Si]

I'll give it a go. Thank you for the help