Results 1 to 2 of 2
  1. #1
    Join Date
    May 2008
    Posts
    24

    Unanswered: Number of Holidays between two date

    Hi,
    I know problem can be simple. I have start date, end date, table with holidays. Problem is I have addition criteria, day(s) of week.
    For example from January 1, 2010 to December 31, 2010 I will work for example Monday, Friday and Saturday. Question is how many days I will work excluding holidays.
    I have to receive in the end three number:
    1. total number of days: this is simple Datediff
    2. Number of potentital work day. For it I made query what count number of Monday, Friday and Saturday.
    3. real number days, so without holidays. For example May 1 is Saturday so not work.

    How can I do it? Possible in simple query?

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    The attached database may help you get this rolling. Code in the module works it way through start date and end date and stores all dates that occur on Monday, Friday or Saturday if the day is not a date found in the Holidays table. These dates are saved in a table, and all Holiday dates that fall on any of the 3 days are stored in another table.
    Queries that begin with "qry" can be used to check your results or get your counts.

    Code:
    Public Holiday_NonWkDay()
    
    Function Test_WKDays()
    'provide the starting and ending dates
    Dim retval
        
        retval = Calc_Potential_Work_Days(#1/1/2010#, #12/31/2010#)
    
    End Function
    
    Function Calc_Potential_Work_Days(startDate As Date, endDate As Date)
    'saves work dates excluding holidays to a table for days named in array workDays()
    'saves holiday dates that fall on any of the days named in workDays()
    Dim i As Integer
    Dim tmpDate As Date
    Dim sqlText As String
    Dim workDays(3) As String, holidayName As String
    Dim workDates()
    Dim db As Database
    Dim rst As Recordset
    ReDim Holiday_NonWkDay(1)
    
    ReDim workDates(1)
    
        workDays(0) = "Monday"
        workDays(1) = "Friday"
        workDays(2) = "Saturday"
    
        If IsNull(startDate) Or IsNull(endDate) Then
            MsgBox "Provide both a start date and end date" & vbCrLf & _
            "in the format mm/dd/yyyy."
            Exit Function
        End If
        
        If startDate > endDate Then
            MsgBox "Start date cannot be greater than End date."
            Exit Function
        End If
        
        tmpDate = startDate
        
        While tmpDate <= endDate
            
            If IsWorkDay(tmpDate, workDays) Then
                workDates(i) = tmpDate
                i = i + 1
                ReDim Preserve workDates(i)
            End If
            
            tmpDate = DateAdd("d", 1, tmpDate)
        
        Wend
        
        '*********************************************************
        sqlText = "DELETE [My Workdays].Work_Dates FROM [My Workdays];"
    
        DoCmd.SetWarnings False
        DoCmd****nSQL sqlText
        DoCmd.SetWarnings True
        
        Set db = CurrentDb()
        Set rst = db.OpenRecordset("My Workdays", dbOpenDynaset)
        
        For i = 0 To UBound(workDates) - 1
            With rst
                .AddNew
                ![Work_Dates] = workDates(i)
                .Update
            End With
        Next i
        '*********************************************************
    
        '*********************************************************
        sqlText = "DELETE [Holiday non-Workdays].Holiday_Date FROM [Holiday non-Workdays];"
    
        DoCmd.SetWarnings False
        DoCmd****nSQL sqlText
        DoCmd.SetWarnings True
        
        Set db = CurrentDb()
        Set rst = db.OpenRecordset("Holiday non-Workdays", dbOpenDynaset)
        
        For i = 0 To UBound(Holiday_NonWkDay) - 2
            holidayName = Nz(DLookup("[Holiday_Name]", _
                "Holidays", "[Holiday_Date] = #" & Holiday_NonWkDay(i) & "#"))
            With rst
                .AddNew
                ![Holiday_On_Workday] = Holiday_NonWkDay(i)
                ![Holiday_Name] = holidayName
                .Update
            End With
        Next i
        '*********************************************************
        
        Set rst = Nothing
        Set db = Nothing
    
    End Function
    
    Function IsWorkDay(inDate As Date, wkDays() As String) As Boolean
    Dim tmpBool As Boolean
    Dim i As Integer
    
        tmpBool = False
        
        For i = 0 To UBound(wkDays) - 1
            If Format(inDate, "dddd") = wkDays(i) Then
                If IsHoliday(inDate) Then
                    tmpBool = False
                    Holiday_NonWkDay(UBound(Holiday_NonWkDay) - 1) = inDate
                    ReDim Preserve Holiday_NonWkDay(UBound(Holiday_NonWkDay) + 1)
                Else
                    tmpBool = True
                End If
                Exit For
            End If
        Next i
    
        IsWorkDay = tmpBool
    
    End Function
    
    Function IsHoliday(targetDate As Date) As Boolean
    Dim srchHoliday As String
    
        srchHoliday = Nz(DLookup("[Holiday_Date]", _
            "Holidays", "[Holiday_Date] = #" & targetDate & "#"))
    
        If srchHoliday = "" Then
            IsHoliday = False
        Else
            IsHoliday = True
        End If
            
    End Function
    Attached Files Attached Files

Posting Permissions

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