You are probably aware we can simply use the Weekday() function to make sure a date does not fall on a weekend. For example, the following routine will check if a given date falls on a weekend (Saturday or Sunday); and if so, we simply return the date for the following Monday.
Select Case WeekDay(InputDate) Case 1 'Input Date falls on a Sunday InputDate = DateAdd("d", 1, InputDate) Case 7 'Input Date falls on a Saturday InputDate = DateAdd("d", 2, InputDate) End Select
I say this article is an extension of the above because I was recently asked to incorporate a check for Holidays as a non-work day. Much like the routines available for calculating work days, we need to use a table listing all the Holidays. Once we have this table, we can use the following routine to check if a date falls on a Holiday and simply return the following day.
If DCount("*", "tblHolidays", "HolidayDate=#" & Format(InputDate, "yyyy-mm-dd") & "#") > 0 Then InputDate = DateAdd("d", 1, InputDate) End If
The problem with simply combining the above two subroutines is which one should we perform first? Let us say we decided to check for a Holiday first and then check for a weekend. If the new date falls on a weekend, the final result will return the date for the following Monday, and we're done. But what if the following Monday happens to be a Holiday?
Conversely, if we check for a weekend first and then followed by a check for a Holiday, we would solve the above problem. But what happens if the input date was on a Friday and it was also Holiday? Checking for a weekend first will fail and then checking for a Holiday will result on a weekend date.
So, as you can see, it is somewhat of a Catch-22 situation.
If you have followed my posts on UtterAccess, you might be familiar with how I like to use recursive functions to solve problems like this one. So, the below function is what I ended up using to continually check if a date falls on a Holiday or a weekend and return the next work day.
Public Function GetNextWorkDay(InputDate As Date) As Date '3/16/2017 'http://thedbguy.blogspot.com 'if input date is on a weekend or a holiday, returns the next work day On Error GoTo errHandler 'check for holiday If DCount("*", "tblHolidays", "HolidayDate=#" & Format(InputDate, "yyyy-mm-dd") & "#") > 0 Then InputDate = GetNextWorkDay(DateAdd("d", 1, InputDate)) End If 'check for weekend Select Case Weekday(InputDate) Case 1 'Input date falls on a Sunday InputDate = GetNextWorkDay(DateAdd("d", 1, InputDate)) Case 7 'Input date falls on a Saturday InputDate = GetNextWorkDay(DateAdd("d", 2, InputDate)) End Select GetNextWorkDay = InputDate errExit: Exit Function errHandler: MsgBox Err.Number & ". " & Err.Description Resume errExit End Function
Hope you find it useful. Please let me know if you have any recommendations for improvement. Thank you for reading.