Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Sep 2003
    Location
    Washington, DC
    Posts
    116

    Unanswered: calculate working days between 2 dates

    Is there a way to calculate the number of working days between 2 dates in MSAccess? Excel has this funtionality (NETWORKDAYS).

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    does excel count holidays? if so, which country's holidays?

    the "best practices" answer to this question (and it does seem to come up all over the place) is to join your data table to a calendar table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2003
    Location
    Washington, DC
    Posts
    116
    Excel will calculate holidays if you tell it what the holidays are. If you do a search in Excel's help file for NETWORKDAYS, you'll see what I'm talking about.

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    If you just want the number of normal "working" days, i.e. Monday thur Friday, use DateDiff with the set to "w". Counting holidays, as indicated above, would be much more problematical; what country, what kind of holidays?

    WorkDaz = DateDiff("w", StartingDate, EndingDate)
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I wrote this a long time ago. I've since converted them to MSSQL sprocs, so I'm not entirely sure they still work as is, but...

    It counts workdays in a given month, and excludes the holidays that
    my company takes. The date passed to the CountWorkDays_Total
    function can be any date, and the function finds the first day of the month,
    and the total days in the month. You would have to make some changes,
    where you'd pass in the begin and end dates.

    The second function checks to see if the current date in the loop is a holiday.

    Code:
    Function CountWorkDays_Total(dtmDate) As Byte
      Dim bCntDay As Byte, dtmTemp As Date, bDaysInMnth As Byte, bLastDay As Byte, dtmFirstDay, intCntWDay As Integer
      bLastDay = DatePart("d", DateAdd("d", -1, CDate(DatePart("m", DateAdd("m", 1, dtmDate)) & "/1/" & DatePart("yyyy", DateAdd("m", 1, dtmDate)))))
      dtmFirstDay = DateAdd("m", -1, CDate(DatePart("m", DateAdd("m", 1, dtmDate)) & "/1/" & DatePart("yyyy", DateAdd("m", 1, dtmDate))))
      intCntWDay = 0
      For bCntDay = 0 To bLastDay - 1
        dtmTemp = DateAdd("d", bCntDay, dtmFirstDay)
        Select Case DatePart("w", dtmTemp)
          Case vbSunday, vbSaturday
            'make sure Xmas or New Years or Jul 4th don't fall in the weekend
            If DatePart("m", dtmTemp) = 1 And DatePart("d", dtmTemp) = 1 Then
              intCntWDay = intCntWDay - 1
            End If
            If DatePart("m", dtmTemp) = 12 And DatePart("d", dtmTemp) = 25 Then
              intCntWDay = intCntWDay - 1
            End If
            If DatePart("m", dtmTemp) = 7 And DatePart("d", dtmTemp) = 4 Then
              intCntWDay = intCntWDay - 1
            End If
          Case Else
            If Not IsHoliday(dtmTemp) Then
              intCntWDay = intCntWDay + 1
            End If
        End Select
      Next bCntDay
      CountWorkDays_Total = intCntWDay
    End Function
    
    Function IsHoliday(dtmDate) As Boolean
      'determines if the passed in date is a holiday
      Dim dtmXmas As Date, dtmNwYrs As Date, dtmJLY4 As Date
      dtmXmas = CDate("12/25/" & Year(dtmDate))
      dtmNwYrs = CDate("1/1/" & Year(dtmDate))
      dtmJLY4 = CDate("7/4/" & Year(dtmDate))
      IsHoliday = False
      If dtmDate = dtmXmas Then
        IsHoliday = True
      ElseIf dtmDate = dtmNwYrs Then
        IsHoliday = True
      ElseIf dtmDate = dtmJLY4 Then
        IsHoliday = True
      Else
        'check for monday holidays
        If DatePart("w", dtmDate) = 2 Then
          'check for May and Sep
          If DatePart("m", dtmDate) = 9 Then
            'check if it's the first Monday
            If DatePart("d", dtmDate) < 8 Then
              IsHoliday = True
            End If
          ElseIf DatePart("m", dtmDate) = 5 Then
            'check if it's the last monday
            If DatePart("d", dtmDate) > 24 Then
              IsHoliday = True
            End If
          End If
        ElseIf DatePart("w", dtmDate) = 5 Then
          If DatePart("m", dtmDate) = 11 Then
            If DatePart("d", dtmDate) >= 21 Then
              '4th Thurs in November=Thanksgiving
              IsHoliday = True
            End If
          End If
        End If
      End If
    End Function
    Inspiration Through Fermentation

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by PatrickFAO
    If you do a search in Excel's help file for NETWORKDAYS, you'll see what I'm talking about.
    okay, i did, and i am seriously underimpressed

    apparently when calling the function, you would have to feed it a list of holiday dates to exclude!!

    how wonderfully useful is that, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2006
    Posts
    157
    I think I used this when workdays included saturdays

    Code:
    Function NetWorkDays(STARTerDATE As Date, ENDerDATE As Date) As Integer
    
    Dim TESTDATE As Date
    
    NetWorkDays = 0
    
    TESTDATE = STARTerDATE
    
    While TESTDATE <= ENDerDATE
    
        If Not (Weekday(TESTDATE) = 7) Then
            NetWorkDays = NetWorkDays + 1
        End If
        
        TESTDATE = TESTDATE + 1
        
        
    Wend
    
    End Function

    if you exclude saturdays too

    turn this

    Code:
     If Not (Weekday(TESTDATE) = 7) Then
    into this

    Code:
     If Not ((Weekday(TESTDATE) = 7) Or (Weekday(TESTDATE) = 1)) Then
    anyway, you still have the weekday function to emulate the networkdays of excel

    you may even add an additional array parameter to the function containing holidays, loop through
    Only quitters quit!

  8. #8
    Join Date
    Jul 2004
    Location
    UK
    Posts
    24
    This is a function I use (based on Mon-Fri working week). It works quicker than cycling through every day, especially if the two dates are quite a way apart.

    Code:
    Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
    'Return number of working days between two dates
    Dim Count As Integer
    
      StartDate = StartDate + 1 'Don't count the first day!
      
      Count = Int((EndDate - StartDate) / 7) 'number of whole weeks
      StartDate = StartDate + (Count * 7)
      Count = Count * 5
      Do While StartDate <= EndDate
        Select Case Weekday(StartDate)
          Case 2 To 6 'Mon - Fri
            Count = Count + 1
        End Select
        StartDate = StartDate + 1
      Loop
      WorkingDays = Count
    
    End Function
    Hope it's useful!

    Qpid.

  9. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Originally posted by r 937:
    apparently when calling the function, you would have to feed it a list of holiday dates to exclude!! how wonderfully useful is that, eh
    And exactly how would you expect any code to exclude holidays without being told what the holidays were, eh? I don't know how things are done in Canada, but in the US we have national, state, local and religious holidays! Some are observed by some companies and others are not. Some occur on the same date each year but most don't. Intelligent software can only can only do so much!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  10. #10
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Qpid,

    In the days before Vb gave us great date handling functions your hack posted above was short and sweet, but why would you want to use now, when

    Workdaz = DateDiff("w", StartingDate, EndingDate) does the same thing?
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Missinglinq
    And exactly how would you expect any code to exclude holidays without being told what the holidays were, eh?
    this is my point exactly!!!

    why would you want to feed this list to every query which needs to calculate working days? why wouldn't you store this very valuable knowledge in a central place where it's easy to see, easy to validate, and easy to maintain? in a calendar table!!

    the calendar table has the added advantage that joining to it is way more efficient than looping in a function
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Sep 2003
    Location
    Washington, DC
    Posts
    116
    I've tried using this function:

    Workdaz = DateDiff("w", StartingDate, EndingDate)

    But this only returns the number of times that specific day (e.g. StartingDate=Monday) has occurred in this time sequence.

  13. #13
    Join Date
    Oct 2007
    Posts
    127
    I have just enterered the following code in MS Access - Macro and saved it...

    Code:
    Function NetWorkDays(STARTerDATE As Date, ENDerDATE As Date) As Integer
    
    Dim TESTDATE As Date
    
    NetWorkDays = 0
    
    TESTDATE = STARTerDATE
    
    While TESTDATE <= ENDerDATE
    
        If Not ((Weekday(TESTDATE) = 7) Or (Weekday(TESTDATE) = 1)) Then
            NetWorkDays = NetWorkDays + 1
        End If
        
        TESTDATE = TESTDATE + 1
        
        
    Wend
    
    End Function
    In the Immediate tab at the bottom I enter the following to check if the code works.

    ?NetWorkDays(01/01/2007,30/09/2007)
    0


    It brings back 0

    What am I doing wrong? Why returning the correct date difference?

    Thanks in advance

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by PatrickFAO
    I've tried using this function:

    Workdaz = DateDiff("w", StartingDate, EndingDate)

    But this only returns the number of times that specific day (e.g. StartingDate=Monday) has occurred in this time sequence.
    what happend if you try to calculate the difference in days rather than weeks?

  15. #15
    Join Date
    Oct 2007
    Posts
    127
    Quote Originally Posted by healdem
    what happend if you try to calculate the difference in days rather than weeks?

    Workdaz = DateDiff("d", StartingDate, EndingDate)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •