Date return from NetworkDays and Holidays

[Paintballlovr]

I realize that this topic is similar to other posts about how to calculate networkdays, but I'm hoping it's different enough that someone might have some input. I've looked through several examples of networkdays functions and if the answer is keep reading, I will, but it's starting to all run together. Here is what I'm looking to do and I'll let it float from there. I have a date and I would like to be able to identify the next date that is not on a weekend or holiday. I have a holiday table and created a fake holiday of 3/23/2015. I would like to be able to enter the date of 3/20/2015 (Friday) and have the system tell me that the "next day" is 3/24/2015, since 3/21 and 3/22 are on the weekend and 3/23 is my made up holiday.

[pbaldy]

I'd have a function that accepted the date as a parameter. It would increment the date by one until the date was neither a weekend nor in the holiday table, and return that date.

[orange]

Here is a function that may help. I can't find a version that deals/accepts holidays, but I have seen one.

You can adjust this as necessary, or you can try googling ISOWorkday or some combination.

'---------------------------------------------------------------------------------------
' Procedure : ISO_WorkdayDiff
' Author    : Gustav
' Created   : 11/11/2009
' Purpose   : Calculate Workdays between 2 dates (Accessd)
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Public Function ISO_WorkdayDiff( _
  ByVal datDateFrom As Date, _
  ByVal datDateTo As Date) _
  As Long

      ' Purpose: Calculate number of working days between dates datDateFrom and datDateTo.
      ' Assumes: 5 or 6 working days per week. Weekend is (Saturday and) Sunday.
      '
      ' Limitation: *** Does not account for public holidays.***
      '
      ' May be freely used and distributed.
      ' 1999-04-23. Gustav Brock, Cactus Data ApS, Copenhagen ' 2000-10-03. Constants added.
      '             Option for 5 or 6 working days per week added.

        Const cbytWorkdaysOfWeek  As Byte = 5

        Dim bytSunday             As Byte
        Dim intWeekdayDateFrom    As Integer
        Dim intWeekdayDateTo      As Integer
        Dim lngDays               As Long
        Dim datDateTemp           As Date
        
        ' Reverse dates if these have been input reversed.
10       On Error GoTo ISO_WorkdayDiff_Error

20      If datDateFrom > datDateTo Then
30        datDateTemp = datDateFrom
40        datDateFrom = datDateTo
50        datDateTo = datDateTemp
60      End If
        
        ' Find ISO weekday for Sunday.
70      bytSunday = Weekday(vbSunday, vbMonday)
        
        ' Find weekdays for the dates.
80      intWeekdayDateFrom = Weekday(datDateFrom, vbMonday)
90      intWeekdayDateTo = Weekday(datDateTo, vbMonday)
        
        ' Compensate weekdays' value for non-working days (weekends).
100     intWeekdayDateFrom = intWeekdayDateFrom + (intWeekdayDateFrom = bytSunday)
110     intWeekdayDateTo = intWeekdayDateTo + (intWeekdayDateTo = bytSunday)
        
        ' Calculate number of working days between the two weekdays, ignoring number of weeks.
120     lngDays = intWeekdayDateTo - intWeekdayDateFrom - (cbytWorkdaysOfWeek * (intWeekdayDateTo < intWeekdayDateFrom))
        ' Add number of working days between the weeks of the two dates.
130     lngDays = lngDays + (cbytWorkdaysOfWeek * DateDiff("w", datDateFrom, datDateTo, vbMonday, vbFirstFourDays))
        
140     ISO_WorkdayDiff = lngDays

150      On Error GoTo 0
160      Exit Function

ISO_WorkdayDiff_Error:

170       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ISO_WorkdayDiff of Module Module5"

End Function

[Paintballlovr]

I apologize, but I don't know how to use the code you provided. Here is the query I'm hoping to integrate it into:

Code:
"SELECT tblMaster_CRMS_CREATION.[Date Created], tblMaster_CRMS_CREATION.[Created By], tblMaster_CRMS_CREATION.[CRM #], tblMaster_CRMS_CREATION.Topic, tblMaster_FC_CRM_STANDARDS.[1_Business_Day] INTO tbltemp_FC_CRMS_Created_Detail
FROM (tblMaster_CRMS_CREATION INNER JOIN tblMaster_FC_CRM_STANDARDS ON tblMaster_CRMS_CREATION.Topic = tblMaster_FC_CRM_STANDARDS.TOPIC) INNER JOIN tblFinCounselor ON tblMaster_CRMS_CREATION.[Created By] = tblFinCounselor.Short_Name
WHERE (((tblMaster_CRMS_CREATION.[Date Created])=#3/20/2015#) AND ((tblMaster_FC_CRM_STANDARDS.[1_Business_Day])=Yes))
ORDER BY tblMaster_CRMS_CREATION.[Created By], tblMaster_CRMS_CREATION.[CRM #];"

[Paintballlovr]

I'm a donkey on the edge! Someone please talk me down from the ledge that has me creating a list of weekdays in excel, deleting the holidays, and just importing it into Access.

[pbaldy]

The code provided doesn't really do what you want, though it includes a couple of relevant pieces. You just need a For/Next loop, basically with a big enough counter to get past a weekend and however many contiguous holiday dates there might be. Within it, add a day to the date input, check if that date is a weekend day (Weekday function), if not check if in the holidays table (DCount function). If neither, return that date and exit, otherwise continue loop.

[Paintballlovr]

I really wanted to keep my process contained to the query design view, so I went with IIF statements. I'm posting what I have to see if anyone sees any holes in the code. I'm currently testing date combinations, so there could be a problem in there and I just haven't found it yet. We do not have consecutive days as holidays, so I'm hopeful this will do the same thing as suggested above. My inexperience with writing code was the driving factor in going this route, but I'm open to any other suggestions (you might have to show the code behind it for me to understand/use it).

target: IIf(Weekday([date created]+1)="7" And [date created]+3 In (SELECT holiday_date FROM tblmaster_holidays),[date created]+4,IIf(Weekday([date created]+1)="7" And [date created]+3 Not In (SELECT holiday_date FROM tblmaster_holidays),[date created]+3,IIf([date created]+1 In (SELECT holiday_date FROM tblmaster_holidays) And Weekday([date created]+2)="7",[date created]+4,IIf([date created]+1 In (SELECT holiday_date FROM tblmaster_holidays),[date created]+2,[date created]+1))))

The days I'm testing are 3/18(wed), 3/19(thurs), and 3/20(Fri). My holiday table shows that 3/19 and 3/23 are holidays so the target day for 3/18 should be 3/20, 3/19 should also have a target of 3/20 and 3/20 should have a target of 3/24

[pbaldy]

Wow, that looks a lot more complicated than a function would have been, and less flexible. In any case, does it work? I believe the Weekday function returns a number, not text.

[Paintballlovr]

So far it works, but just in my first scenario ( I haven't ran any others yet). Part of what I'm worried about is that I have written something designed for my test rather than something that my test validates. I agree that if this works, it's not simple or pretty, but since I don't have the ability to just bang out VBA and in the absence of an alternative I could modify to work for my needs I had to go back to what I'm familiar with. You are correct on my use of the weekday function, I'm using it as my version of networkdays, identifying where Saturday is so I can avoid it and move to the next weekday. Assuming this works out, I get to go through it again to modify it to target two business days out as well.

[pbaldy]

See if this works. it's untested, because I'm too lazy to build the holiday table.

Public Function GetNextBusinessDay(dteDate As Date) As Date  
  Dim x As Integer
  Dim dteTestDate As Date


  dteTestDate = dteDate + 1


  For x = 1 To 10
    If Weekday(dteTestDate, vbMonday) < 6 Then 'test date is a weekday, see if it's  holiday
      If DCount("*", "tblmaster_holidays", "holiday_date = #" & dteTestDate & "#") = 0 Then 'it's not a holiday
        GetNextBusinessDay = dteTestDate
        Exit For
      End If
    End If
    
    'if we got here, test date is weekend or holiday, add a day and retest
    dteTestDate = dteTestDate + 1
  Next x


End Function