• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 432
  • Last Modified:

Determine the date of the business day from the business day number

I can determine the business day to date of the month, that is, if I pass in the start date adn end date.  Howver, how would I go about determining the business DATE if I pass in the business day number?  That is, if I have a DB of 5, how do I get the date this business day number represents?  I have a holiday schedule and this must all be done in VBA, Access 2003.
0
ssmith94015
Asked:
ssmith94015
  • 4
  • 3
  • 2
  • +1
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
in plain English
what is a DB of 5?
0
 
Aaron TomoskyTechnology ConsultantCommented:
You mean date + 5 business days? So if today is Wednesday and fri is a holiday then 5 business days later is next Thursday?
0
 
ssmith94015Author Commented:
Sorry, I get so wrapped up i forget when I use company vernacular.  BD is business day, which excludes weekends and holidays.  Yes, aarontomosky, you got it.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
ssmith94015Author Commented:
Basically, the approach is between, say 4/1/2011 and 4/30/2011, what date is BD 5? or whatever BD number is inputted.

Sandra
0
 
Rey Obrero (Capricorn1)Commented:
with the date between  4/1/2011 and 4/30/2011, what should be the date  with BD 5?

is it 4/1/2011 + 5 BD  = 4/7/2011 ?
0
 
Rey Obrero (Capricorn1)Commented:
try this function

Function GetDueDate(dDate As Date, Span As Integer) As Date
Dim j As Integer, i As Integer, dtStart
dtStart = dDate
For j = 1 To Span
    dtStart = dtStart + 1
    Do While Weekday(dtStart) = 1 Or Weekday(dtStart) = 7 _
        Or Not IsNull(DLookup("observedDate", "tblHolidays", "observedDate=#" _
     & dtStart & "#"))
        dtStart = dtStart + 1
        i = i + 1
    Loop
Next
GetDueDate = DateAdd("d", (Span + i), dDate)

End Function

Open in new window



to use , pass the starting date and number of BD

getDueDate(#4/1/2011#,5)


0
 
Gustav BrockCIOCommented:
Use the function below:

datLaterBD = ISO_WorkdayAdd(#4/1/2011#, BD, 5, True)


Public Function ISO_WorkdayAdd( _
  ByVal datDateFrom As Date, _
  ByVal lngWorkdaysAdd As Long, _
  Optional ByVal bytWorkdaysOfWeek As Byte = 5, _
  Optional ByVal booExcludeHolidays As Boolean) _
  As Date

' Purpose: Add number of working days to date datDateFrom.
' Assumes: 1 to 7 working days per week.
'          First workday is Monday.
'          Weekend is up to and including Sunday.
' Requires: ISO_WorkdayDiff
'           DateSkipWeekend
' 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.
' 2002-01-10. Option for 1 to 7 working days per week added.
'             Allowed to add negative number of working days.
'             Adding of zero working days returns the next
'             working day if current day is not a working day.
' 2008-06-14. Option to add holidays in the period to the count of workdays.
' 2011-03-23. Included a call to DateSkipWeekend to calculate correctly for a
'             holiday ending right before a weekend at the end of the period.
'
' Test:
' For j = 0 To 10 : For i = 0 to 12 : ? j, i, ISO_WorkdayAdd(Date + j, i): Next i: Next j

  ' Minimum and maximum count of workdays per week.
  Const cbytWorkdaysCountMin  As Byte = 1
  Const cbytWorkdaysCountMax  As Byte = 7

  Dim datDateTo               As Date
  Dim bytMonday               As Byte
  Dim bytSunday               As Byte
  Dim intWeekdayFirst         As Integer
  Dim intWorkdayLast          As Integer
  Dim intDaysShift            As Integer
  Dim lngDays                 As Long
  Dim lngWeeks                As Long
  Dim lngWorkdays             As Long
  Dim lngWorkdaysDiff         As Long
  
  On Error GoTo Err_ISO_WorkdayAdd
  
  datDateTo = datDateFrom
  lngWorkdays = lngWorkdaysAdd
  If bytWorkdaysOfWeek >= cbytWorkdaysCountMin And bytWorkdaysOfWeek <= cbytWorkdaysCountMax Then
    ' Find ISO weekday for Monday.
    bytMonday = WeekDay(vbMonday, vbMonday)
    ' Find ISO weekday for Sunday.
    bytSunday = WeekDay(vbSunday, vbMonday)
    ' Find ISO weekday for last workday.
    intWorkdayLast = bytMonday + bytWorkdaysOfWeek - 1
    
    ' Find ISO weekday for date datDateTo.
    intWeekdayFirst = WeekDay(datDateTo, vbMonday)
    ' Shift date datDateTo from weekend to Monday.
    If intWeekdayFirst > intWorkdayLast Then
      If lngWorkdaysAdd >= 0 Then
        datDateTo = DateAdd("d", bytSunday - intWeekdayFirst + 1, datDateTo)
      Else
        datDateTo = DateAdd("d", intWorkdayLast - intWeekdayFirst, datDateTo)
      End If
      ' Find ISO weekday for shifted date datDateTo.
      intWeekdayFirst = WeekDay(datDateTo, vbMonday)
    End If
    
    ' Calculate number of days date datDateTo shall be shifted.
    If lngWorkdaysAdd >= 0 Then
      ' Shift to proceeding Monday in current week.
      intDaysShift = intWeekdayFirst - bytMonday
    Else
      ' Shift to succeeding last workday in current week.
      intDaysShift = intWeekdayFirst - intWorkdayLast
    End If
    ' Shift date datDateTo.
    datDateTo = DateAdd("d", -intDaysShift, datDateTo)
    ' Calculate workdays to add from start/end of current work week.
    lngWorkdaysAdd = lngWorkdaysAdd + intDaysShift
    
    ' Calculate number of workweeks and additional workdays to add.
    lngWeeks = lngWorkdaysAdd \ bytWorkdaysOfWeek
    lngDays = lngWorkdaysAdd Mod bytWorkdaysOfWeek
    
    ' Add number of calendar weeks and additional calendar days to
    ' shifted date datDateTo.
    If lngWeeks <> 0 Then
      datDateTo = DateAdd("ww", lngWeeks, datDateTo)
    End If
    If lngDays <> 0 Then
      datDateTo = DateAdd("d", lngDays, datDateTo)
    End If
    
    If booExcludeHolidays = True Then
      While lngWorkdays - lngWorkdaysDiff > 0
        lngWorkdaysDiff = ISO_WorkdayDiff(datDateFrom, datDateTo, True)
        datDateTo = DateAdd("d", lngWorkdays - lngWorkdaysDiff, datDateTo)
        datDateTo = DateSkipWeekend(datDateTo)
      Wend
    End If

  End If
  
  ISO_WorkdayAdd = datDateTo
  
Exit_ISO_WorkdayAdd:
  Exit Function
  
Err_ISO_WorkdayAdd:
  ' Date datDateTo + lngWorkdaysAdd is outside date range of Access.
  ' Return time zero, 00:00:00.
  Resume Exit_ISO_WorkdayAdd

End Function



Public Function ISO_WorkdayDiff( _
  ByVal datDateFrom As Date, _
  ByVal datDateTo As Date, _
  Optional ByVal booExcludeHolidays As Boolean) _
  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.
' 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.
' 2008-06-12. Option to exclude holidays from the count of workdays.

  Const cbytWorkdaysOfWeek  As Byte = 5
  ' Name of table with holidays.
  Const cstrTableHoliday    As String = "tblHoliday"
  ' Name of date field in holiday table.
  Const cstrFieldHoliday    As String = "HolidayDate"

  Dim bytSunday             As Byte
  Dim intWeekdayDateFrom    As Integer
  Dim intWeekdayDateTo      As Integer
  Dim lngDays               As Long
  Dim datDateTemp           As Date
  Dim strDateFrom           As String
  Dim strDateTo             As String
  Dim lngHolidays           As Long
  Dim strFilter             As String
  
  ' Reverse dates if these have been input reversed.
  If datDateFrom > datDateTo Then
    datDateTemp = datDateFrom
    datDateFrom = datDateTo
    datDateTo = datDateTemp
  End If
  
  ' Find ISO weekday for Sunday.
  bytSunday = WeekDay(vbSunday, vbMonday)
  
  ' Find weekdays for the dates.
  intWeekdayDateFrom = WeekDay(datDateFrom, vbMonday)
  intWeekdayDateTo = WeekDay(datDateTo, vbMonday)
  
  ' Compensate weekdays' value for non-working days (weekends).
  intWeekdayDateFrom = intWeekdayDateFrom + (intWeekdayDateFrom = bytSunday)
  intWeekdayDateTo = intWeekdayDateTo + (intWeekdayDateTo = bytSunday)
  
  ' Calculate number of working days between the two weekdays, ignoring number of weeks.
  lngDays = intWeekdayDateTo - intWeekdayDateFrom - (cbytWorkdaysOfWeek * (intWeekdayDateTo < intWeekdayDateFrom))
  ' Add number of working days between the weeks of the two dates.
  lngDays = lngDays + (cbytWorkdaysOfWeek * DateDiff("w", datDateFrom, datDateTo, vbMonday, vbFirstFourDays))
  
  If booExcludeHolidays And lngDays > 0 Then
    strDateFrom = Format(datDateFrom, "yyyy\/mm\/dd")
    strDateTo = Format(datDateTo, "yyyy\/mm\/dd")
    strFilter = cstrFieldHoliday & " Between #" & strDateFrom & "# And #" & strDateTo & "# And Weekday(" & cstrFieldHoliday & ", 2) <= " & cbytWorkdaysOfWeek & ""
    lngHolidays = DCount("*", cstrTableHoliday, strFilter)
  End If
  
  ISO_WorkdayDiff = lngDays - lngHolidays

End Function



Public Function DateSkipWeekend( _
  ByVal datDate As Date, _
  Optional ByVal booReverse As Boolean) _
  As Date

' Purpose: Calculate first working day equal to or following/preceding datDate.
' Assumes: 5 or 6 working days per week. Weekend is (Saturday and) Sunday.
' Limitation: Does not count for public holidays.
'
' May be freely used and distributed.
' 1999-07-03, Gustav Brock, Cactus Data ApS, Copenhagen
  
  Const cintWorkdaysOfWeek As Integer = 5

  Dim bytSunday   As Byte
  Dim bytWeekday  As Byte
  
  bytSunday = WeekDay(vbSunday, vbMonday)
  bytWeekday = WeekDay(datDate, vbMonday)
  
  If bytWeekday > cintWorkdaysOfWeek Then
    ' Weekend.
    If booReverse = False Then
      ' Get following workday.
      datDate = DateAdd("d", 1 + bytSunday - bytWeekday, datDate)
    Else
      ' Get preceding workday.
      datDate = DateAdd("d", cintWorkdaysOfWeek - bytWeekday, datDate)
    End If
  End If

  DateSkipWeekend = datDate

End Function

Open in new window


You may need to adjust the names of the holiday table and field to those of yours.

/gustav
0
 
ssmith94015Author Commented:
Have been pulled to another problem, but will try these suggestions out tomorrow.

Sandra
0
 
ssmith94015Author Commented:
Actually they both worked, but for different clients and different reasons.  Am splitting as I did use both solutions - However, the more complex did allow greater flexiblity in some of the situtations I needed so slightly more went that direction.  Thank you both.

Sandra
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now