473,881 Members | 1,704 Online

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

2 New Member
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:

Expand|Select|Wrap|Line Numbers
1. ' Accepts a start date and an integer(numDays) and calculates the date that is "numDays"
2. ' number of work(week) days after the start date.
3.
4. Public Function Workdays(ByRef startDate As Date, ByRef numDays As Integer) As Date
5.
6. Dim weekHold As Integer
7. Dim dateHold As Date
8. Dim dayHold As Integer
9.
10. Dim days As Integer
11. days = numDays Mod 5
12. dayHold = days
13.
14. ' if due date will fall on a sunday, advance two days.
15. Select Case days
16. ' when you add 1 day and x weeks to the startDate to get the due date
17.     Case 1
18.       Select Case DatePart("w", startDate) + days
19.       ' sunday and monday
20.         Case 1, 2
21.             dayHold = days + 1
22.         ' saturday
23.         Case 7
24.             dayHold = days + 2
25.         ' else
26.         Case Else
27.             dayHold = days
28.         End Select
29. ' when you add 2 days and x weeks to the startDate to get the due date
30.     Case 2
31.         Select Case DatePart("w", startDate) + days
32.         ' sunday, monday and saturday
33.             Case 1, 2, 7
34.                 dayHold = days + 2
35.         ' tuesday
36.             Case 3
37.                 dayHold = days + 1
38.             Case Else
39.                 dayHold = days
40.             End Select
41. ' when you add 3 days and x weeks to the startDate to get the due date
42.     Case 3
43.         Select Case DatePart("w", startDate) + days
44.         ' wednesday
45.             Case 4
46.                 dayHold = days + 1
47.         ' Thursday and friday
48.             Case 5, 6
49.                 dayHold = days
50.             Case Else
51.                 dayHold = days + 2
52.             End Select
53. ' when you add 4 days and x weeks to the startDate to get the due date
54.     Case 4
55.         Select Case DatePart("w", startDate) + days
56. ' wednesday
57.             Case 4
58.                 dayHold = days + 1
59. ' thursday and friday
60.             Case 5, 6
61.                 dayHold = days
62.             Case Else
63.                 dayHold = days + 2
64.             End Select
65. ' only case left is case where you just add a number of weeks to the startDate to get due date.
66.     Case 0
67.         dayHold = 7
68. End Select
69.
70. 'calculate due date
71. weekHold = numDays / 5
72. If weekHold > 0 Then
73.     weekHold = weekHold - 1
74. End If
75.
76. dateHold = DateAdd("ww", weekHold, startDate)
77. dateHold = DateAdd("d", dayHold, dateHold)
78. Workdays = dateHold
79.
80. End Function
81.
Dec 13 '10 #1
3 2731
8,834 Recognized Expert Expert
Unless I am totally misinterpreting this Thread, to calculate a Date that is numDays of Weekdays from a startDate and return a Date Value:
Expand|Select|Wrap|Line Numbers
1. Public Function Workdays(ByRef startDate As Date, ByRef numDays As Integer) As Date
2. Dim intWkDate As Integer            'Will track the Number of Weekdays accumulated
3. Dim dteDate As Date
4. Dim intDayCtr As Integer            'Will track Overall Days
5.
6. If numDays = 0 Then Exit Function
7.
8. Do
9.  intDayCtr = intDayCtr + 1                          'Holds overall Date Increments
10.   dteDate = DateAdd("d", intDayCtr, startDate)
11.     If Not Weekday(dteDate) Mod 7 < 2 Then          'Yep it is a Weekday, increment Weekday Counter
12.       intWkDay = intWkDay + 1
13.     End If
14. Loop Until intWkDay = numDays                       'Is Number of weekdays = numDays?
15. 'When you get here, the Number of Workdays will equal the numDays Value. The
16. 'actual Date has been sequentially stored in dteDate
17. Workdays = dteDate
18. End Function
Dec 14 '10 #2
Jared Elyea
2 New Member
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
Dec 14 '10 #3