I think I'm getting close? Find next business day by skipping weekend and holidays

[ST4RCUTTER]

This problem has been brought up before and solved in a number of ways. I have been working on a module to do this...but in my own unique way. Here is the grist of it. I am trying to setup several textboxes with "target dates" that will give me the next business date (Mon - Fri). The textboxes will be setup with the function =NextBizDay(x) where "x" is the number of days to skip. For example, there are two target dates:

Send Design Docs Date = FOC date + 5days Example: NextBizDay(5)​

Receive Design Docs Date =FOC date + 10days Example: NextBizDay(10)​

To make matters interesting, there can be an Original FOC date or multiple supplementary FOC dates if the project slips. In the latter case I had to find the last Supplementary FOC date. These dates are stored in two different tables.

----------------------------------------------------------
My NextBizDay function will first determine the last Supplemental FOC date if it exists and assign it to variable "MyDate". If it doesn't exist then MyDate assumes the value of the project's "Original FOC Date". (This works!)

(2) The function then tests to see if MyDate is on a Saturday or Sunday or if it falls on a holiday found in tbl_Holidays. If it falls on (Sun, Mon, Tues, Wed, Thrs, or a holiday it adds 1 day. If MyDate is on a Friday it adds 3 days. If MyDate is on a Saturday it adds 2 days. My function is giving me the error, "Compile Error: Statements and labels invalid between Select Case and first Case "

Here is the code:

Option Compare Database

Function NextBizDay(intAddDays As Integer)

Dim FDate As Date
Dim MSDate As Date

'Determine value of Start Date (Firm Order Commitment Date or Supplemental FOC Date)
FDate = Nz(DLookup("[qry_form_lastFOC]![LastDate]", "qry_form_lastFOC", "[qry_form_lastFOC]![LastFOC] > 0")) 'Checks for possible existence of Sup FOC
ODate = DLookup("[DateEntered]", "qry_date_targets", "[qry_date_targets]![DateRefID]= 38") 'Finds value of Original FOC in table which always exists

If FDate = 0 Then MyDate = ODate Else MyDate = FDate 'Sets Sup FOC as MyDate if available

For i = i To intAddDays   'Loops for the number of days requested.

Wkd = Weekday(MyDate)

Select Case Wkd = 1      'Check for Sunday
      MyDate = DateAdd("d", 1, MyDate)
      Holidays (MyDate)
      Case Wkd = 2
      MyDate = DateAdd("d", 1, MyDate)
      Holidays (MyDate)
      Case Wkd = 3
      MyDate = DateAdd("d", 1, MyDate)
      Holidays (MyDate)
      Case Wkd = 4
      MyDate = DateAdd("d", 1, MyDate)
      Holidays (MyDate)
      Case Wkd = 5
      MyDate = DateAdd("d", 1, MyDate)
      Holidays (MyDate)
      Case Wkd = 6
      MyDate = DateAdd("d", 3, MyDate)
      Holidays (MyDate)
      Case Wkd = 7
      MyDate = DateAdd("d", 2, MyDate)
      Holidays (MyDate)
End Select

Next i

MsgBox MyDate

End Function


Public Function Holidays(chkDate As Date)

       'This will check the value of MyDate against the table of company holidays and add a day if any matches are found.
    If chkDate = DLookup("[tbl_Holidays]![HolidayDate]", "tbl_Holidays", "[tbl_Holidays]![HolidayDate] = chkDate") Then MyDate = DateAdd("d", 1, MyDate) Else MyDate = MyDate
       
End Function

 

[ecawilkinson]

hi,

try:

Select Case Wkd
      Case 1      'Check for Sunday
      MyDate = DateAdd("d", 1, MyDate)
      Holidays (MyDate)
      Case 2
      MyDate = DateAdd("d", 1, MyDate)
      Holidays (MyDate)
      Case 3
      MyDate = DateAdd("d", 1, MyDate)
      Holidays (MyDate)
      Case 4
      MyDate = DateAdd("d", 1, MyDate)
      Holidays (MyDate)
      Case 5
      MyDate = DateAdd("d", 1, MyDate)
      Holidays (MyDate)
      Case 6
      MyDate = DateAdd("d", 3, MyDate)
      Holidays (MyDate)
      Case 7
      MyDate = DateAdd("d", 2, MyDate)
      Holidays (MyDate)
End Select

Hope this helps.

Chris

 

[RuralGuy]

Maybe you can make use of this function:

Public Function PlusWorkdays(dteStart As Date, intNumDays As Long) As Date

PlusWorkdays = dteStart
Do While intNumDays > 0
     PlusWorkdays = DateAdd("d", 1, PlusWorkdays)
'     If Weekday(PlusWorkdays, vbMonday) <= 5 Then
'-- If you have a Holiday Table use the next IF instead!
     If Weekday(PlusWorkdays, vbMonday) <= 5 And _
      IsNull(DLookup("[HoliDate]", "tblHolidays", _
      "[HoliDate] = " & Format(PlusWorkdays, "\#mm\/dd\/yyyy\#;;;\N\u\l\l"))) Then
      '-- The above Format of PlusWorkdays works with US or UK dates!
          intNumDays = intNumDays - 1
     End If
Loop

End Function

 

[ST4RCUTTER]

Thanks RuralGuy,

I went with your code because it seemed most elegant and solved the problem rather well. I am having one embarrasing little problem though. When I set the Control Source of the text field to "=NextBizDay(10)" I don't get the value that the function is returning. The text field remains blank. I must be assigning this value to the field incorrectly. When I display the variable PlusWorkingDays using Msgbox it shows the correctly calculated date. How else does one set a text field to the output of a variable in a function or sub?





Thanks!
ST4R,

 

[RuralGuy]

try:
=PlusWorkdays([YourOtherDateField],10)

What is the NextBizDay function?

 

[ST4RCUTTER]

Sorry for not posting the updated code and causing confusion. Here is the updated code:

Option Compare Database

Public Function NextBizDay(intNumDays As Long)

Dim FSDate As Date 'FOC Supplemental date
Dim FODate As Date 'FOC Original date
Dim MyDate As Date 'Last FOC

'Determine value of Start Date (Firm Order Commitment Date or Supplemental FOC Date)
FSDate = Nz(DLookup("[qry_form_lastFOC]![LastDate]", "qry_form_lastFOC", "[qry_form_lastFOC]![LastFOC] > 0")) 'Checks for possible existence of Sup FOC
FODate = DLookup("[DateEntered]", "qry_date_targets", "[qry_date_targets]![DateRefID]= 38") 'Finds value of Original FOC in table which always exists

If FSDate = 0 Then MyDate = FODate Else MyDate = FSDate 'Sets Sup FOC as MyDate if available

Workdays = MyDate
Do While intNumDays > 0
     Workdays = Workdays - 1
     If Weekday(Workdays, vbMonday) <= 5 And _
      IsNull(DLookup("[HolidayDate]", "tbl_Holidays", _
      "[HolidayDate] = " & Format(Workdays, "\#mm\/dd\/yyyy\#;;;\N\u\l\l"))) Then
       intNumDays = intNumDays - 1
     End If
Loop

End Function

Now I modified this slightly from my earlier code. This is actually subtracting days instead of adding them because I realized that most of my calculations were for business days before the FOC date. You can subtract from the variable "Workdays" just as easily as add and it works just fine. If I place the statement "Msgbox Workdays" after the Loop statement then I receive the correct date. I just can't get the text field to take on the value of "Workdays".



Thanks!
ST4R,

 

[RuralGuy]

See if this functions works for you:

Public Function NextBizDay(intNumDays As Long) As Date

   Dim FSDate As Date   'FOC Supplemental date
   Dim FODate As Date   'FOC Original date

'Determine value of Start Date (Firm Order Commitment Date or Supplemental FOC Date)
   FSDate = Nz(DLookup("[LastDate]", "qry_form_lastFOC", _
                       "[LastFOC] > 0"), 0)  'Checks for possible existence of Sup FOC
   FODate = DLookup("[DateEntered]", "qry_date_targets", _
                    "[DateRefID]= 38") 'Finds value of Original FOC in table which always exists

   If FSDate = 0 Then
      NextBizDay = FODate
   Else
      NextBizDay = FSDate   'Sets Sup FOC as MyDate if available
   End If

   Do While intNumDays > 0
      NextBizDay = NextBizDay - 1
      If Weekday(NextBizDay, vbMonday) <= 5 And _
         IsNull(DLookup("[HolidayDate]", "tbl_Holidays", _
                        "[HolidayDate] = " & Format(NextBizDay, "\#mm\/dd\/yyyy\#;;;\N\u\l\l"))) Then
         intNumDays = intNumDays - 1
      End If
   Loop

End Function

 

[ST4RCUTTER]

That worked RuralGuy!

Thank you so much. Initially it failed but I soon realized that you missed changing the Workdays variable to "NextBizDay" in the statement where it checks to see if the Weekday is <= 5 and where the format is applied for the Dlookup. Not a big deal...

So I guess what I needed to do was to set the NextBizDay variable equal to the value I was looking for instead of using the variable "Workdays"? I suppose you could also have a statement at the end such as:

Workdays = NextBizDay

...and you would arrive at the same conclusion?



ST4R,

 

[RuralGuy]

You are correct but your DLookup() statements also needed changing as you can see.

 

[gemma-the-husky]

in your function

Public Function NextBizDay(intNumDays As Long)

do you not have declare a type of variable that is to be returned. eg date

 

[ST4RCUTTER]

gemma-the-husky,

See I thought that "As Date" portion of the declaration for NextBizDay() would define NextBizDay as a date...

Public Function PrevBizDay(intNumDays As Long) [b][COLOR="Red"]As Date[/COLOR][/b]