By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,492 Members | 936 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,492 IT Pros & Developers. It's quick & easy.

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

P: 2
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

✓ answered by 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:
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

Share this Question
Share on Google+
3 Replies

ADezii
Expert 5K+
P: 8,750
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

P: 2
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

ADezii
Expert 5K+
P: 8,750
I think that it was simply a matter of over-complicating the problem and arriving at the incorrect Logic. That will come with experience.
Dec 14 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.