How to calculate a date that is x weekdays from a start date?

[Jared Elyea]

I had no experience in VB before writing this so bare with me...
the function below seems to work for certain cases, but not all (in some cases it is a week behind). Can anybody help me figure this out? It is supposed to take an integer(numDays ) and a date and calculate the date that is "numDays" weekdays from the start date. I don't know if it is the if statement at the bottom or the particular cases that seem not to work.

Here it is it seems to work for (days) cases 0, 3 and 4, but not for 1 or 2:

' Accepts a start date and an integer(numDays) and calculates the date that is "numDays"
' number of work(week) days after the start date.

Public Function Workdays(ByRef startDate As Date, ByRef numDays As Integer) As Date

Dim weekHold As Integer
Dim dateHold As Date
Dim dayHold As Integer

Dim days As Integer
days = numDays Mod 5
dayHold = days

' if due date will fall on a sunday, advance two days.
Select Case days
' when you add 1 day and x weeks to the startDate to get the due date
    Case 1
      Select Case DatePart("w", startDate) + days
      ' sunday and monday
        Case 1, 2
            dayHold = days + 1
        ' saturday
        Case 7
            dayHold = days + 2
        ' else
        Case Else
            dayHold = days
        End Select
' when you add 2 days and x weeks to the startDate to get the due date
    Case 2
        Select Case DatePart("w", startDate) + days
        ' sunday, monday and saturday
            Case 1, 2, 7
                dayHold = days + 2
        ' tuesday
            Case 3
                dayHold = days + 1
            Case Else
                dayHold = days
            End Select
' when you add 3 days and x weeks to the startDate to get the due date
    Case 3
        Select Case DatePart("w", startDate) + days
        ' wednesday
            Case 4
                dayHold = days + 1
        ' Thursday and friday
            Case 5, 6
                dayHold = days
            Case Else
                dayHold = days + 2
            End Select
' when you add 4 days and x weeks to the startDate to get the due date
    Case 4
        Select Case DatePart("w", startDate) + days
' wednesday
            Case 4
                dayHold = days + 1
' thursday and friday
            Case 5, 6
                dayHold = days
            Case Else
                dayHold = days + 2
            End Select
' only case left is case where you just add a number of weeks to the startDate to get due date.
    Case 0
        dayHold = 7
End Select
    
'calculate due date
weekHold = numDays / 5
If weekHold > 0 Then
    weekHold = weekHold - 1
End If

dateHold = DateAdd("ww", weekHold, startDate)
dateHold = DateAdd("d", dayHold, dateHold)
Workdays = dateHold

End Function

[ADezii]

Unless I am totally misinterpreting this Thread, to calculate a Date that is numDays of Weekdays from a startDate and return a Date Value:

Public Function Workdays(ByRef startDate As Date, ByRef numDays As Integer) As Date
Dim intWkDate As Integer            'Will track the Number of Weekdays accumulated
Dim dteDate As Date
Dim intDayCtr As Integer            'Will track Overall Days

If numDays = 0 Then Exit Function

Do
 intDayCtr = intDayCtr + 1                          'Holds overall Date Increments
  dteDate = DateAdd("d", intDayCtr, startDate)
    If Not Weekday(dteDate) Mod 7 < 2 Then          'Yep it is a Weekday, increment Weekday Counter
      intWkDay = intWkDay + 1
    End If
Loop Until intWkDay = numDays                       'Is Number of weekdays = numDays?
'When you get here, the Number of Workdays will equal the numDays Value. The
'actual Date has been sequentially stored in dteDate
Workdays = dteDate
End Function

[Jared Elyea]

This looks a little nicer than mine! (Okay, a lot). Did you have any idea where I went wrong (aside from not looping and saving a lot of work). If you don't care to analyze it, that is okay. I was just wondering if you noticed anything for the sake of learning from my mistakes.

Thanks a million for your help

[ADezii]

I think that it was simply a matter of over-complicating the problem and arriving at the incorrect Logic. That will come with experience.