Access Work Days + Holidays Code Help!!!

[tdpman]

OK guys and gals, I have a piece of code for Access that calculates the number of work days between 2 given dates. However, this code does not take into account any Holidays that may exist. Can someone please show me how to modify or write new code that can calculate the number of workdays between 2 dates and take into account a list of holidays from say a "Holiday Table"?

The code I have is this:

**************************

Public Function CountWeekDays(SLADate As Date, DevDate As Date) As Integer
' count the number of week days between two dates

Dim CheckDate As Date
CountWeekDays = 0
CheckDate = SLADate
Do Until CheckDate > DevDate
If WeekDay(CheckDate) <> 1 And WeekDay(CheckDate) <> 7 Then
CountWeekDays = CountWeekDays + 1
End If
CheckDate = CheckDate + 1
Loop
End Function
***************************

Any help you can give will be greatly appreciated!!! Thanks!

 

[3587]

You can use this code. You will need to create a table to hold the holidays(HoliName) you want to include and the date(HoliDate) they fall on.

Public Function DeltaDays(StartDate As Date, EndDate As Date) As Integer

    'Get the number of workdays between the given dates

    Dim dbs As Database
    Dim rstHolidays As Recordset

    Dim Idx As Long
    Dim MyDate As Date
    Dim NumDays As Long
    Dim strCriteria As String
    Dim NumSgn As String * 1

    Set dbs = CurrentDb
    Set rstHolidays = dbs.OpenRecordset("tblHolidays", dbOpenDynaset)

    NumSgn = Chr(35)

    MyDate = Format(StartDate, "Short Date")

    For Idx = CLng(StartDate) To CLng(EndDate)
        Select Case (WeekDay(MyDate))
            Case Is = 1     'Sunday
                'Do Nothing, it is NOT a Workday

            Case Is = 7     'Saturday
                'Do Nothing, it is NOT a Workday

            Case Else       'Normal Workday
                strCriteria = "[HoliDate] = " & NumSgn & MyDate & NumSgn
                rstHolidays.FindFirst strCriteria
                If (rstHolidays.NoMatch) Then
                    NumDays = NumDays + 1
                 Else
                    'Do Nothing, it is NOT a Workday
                End If

        End Select

        MyDate = DateAdd("d", 1, MyDate)

    Next Idx

    DeltaDays = NumDays

End Function

Good Luck, hope this helps.

 

[CVigil]

I've named your table for you, and the fields. You can alter what you'd like of course, but here's one way I can see doing it.

Table:
  tblHolidays
Field1:
  dteHolidayDate
  Type: Date
  Required: Yes
  Unique: No
          (Could have multiple holidays on same date)
Field2:
  strHolidayName
  Type: Text
  Required: Yes
  Unique: No
          (Could have holidays spanning multiple dates, even if just for company purposes, such as extra days off.)      
Primary Key: dteHolidayDate AND strHolidayName
             (see Unique property for fields)

You can use the holiday name (or an index field) as a way to get all days associated with a holiday which spans multiple dates.

If the table and field names are as above, use this code as your new function:

'***************************
Public Function CountNonHolidayWeekDays(SLADate As Date, DevDate As Date) As Integer
' count the number of non-holiday week days between two dates
   
   Dim CheckDate As Date
   CountWeekDays = 0
   CheckDate = SLADate
   Do Until CheckDate > DevDate
       If (WeekDay(CheckDate) <> 1) And _
          (WeekDay(CheckDate) <> 7) And _
          (Dcount(&quot;dteHolidayDate&quot;, _
                  &quot;tblHolidays&quot;, _
                  &quot;[dteHolidayDate]=&quot; & CheckDate)=0) Then
       CountWeekDays = CountWeekDays + 1
       End If
   CheckDate = CheckDate + 1
   Loop
End Function
'***************************

I read that the &quot;D&quot; functions, such as DCount, aren't the best in terms of performance, but they work, and in limited-use contexts, I haven't seen a problem. And it is shorter, simpler coding than setting up your own recordset from a query and using the results of that -- which would certainly also work.

Have a good one, tdpman! -- C Vigil =)
(Before becoming a member, I also signed on several posts as
&quot;JustPassingThru&quot; and &quot;QuickieBoy&quot; -- as in &quot;Giving Quick Answers&quot

 

[CVigil]

Cool! Looks like someone else was typing as I was, and their post got in before mine (it wasn't there when I started). Good job, 3587.

And for posterity (future readers), I didn't test or read to determine whether adding 1 to a date value actually adds exactly one day. I just took it from tdpman, who said his existing code worked for non-holiday situations, that &quot;CheckDate = CheckDate + 1&quot; works as expected in context. Certainly, it should be faster than calling a function to do the same. But &quot;MyDate = DateAdd(&quot;d&quot;, 1, MyDate)&quot; is certainly clear, and it certainly works. The DateAdd function is what I'd have used if I weren't just copying tdpman's function and adding a bit.

Have a good one, folks! -- C Vigil =)
(Before becoming a member, I also signed on several posts as
&quot;JustPassingThru&quot; and &quot;QuickieBoy&quot; -- as in &quot;Giving Quick Answers&quot