Databases
StackOverflow2013
Postsdbo
POcalculate date add, but only weekdays
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POcalculate date add, but only weekdays
    text
    copied!<p>I would like to calculate a new date simply by using the build-in dateadd function, but take into account that only weekdays should be counted (or 'business days' so to speak).</p> <p>I have come up with this simple algorithm, which does not bother about holidays and such. I have tested this with some simple dates, but would like some input if this can be done in better ways.</p> <p>This sample assumes a week with 5 business days, monday-friday, where first day of the week is monday. Dateformatting used here is d-m-yyyy, the sample calculates with a startdate of october 1, 2009.</p> <p>Here is the simple form:</p> <pre><code>Dim d_StartDate As DateTime = "1-10-2009" Dim i_NumberOfDays As Integer = 12 Dim i_CalculateNumberOfDays As Integer If i_NumberOfDays &gt; (5 - d_StartDate.DayOfWeek) Then i_CalculateNumberOfDays = i_NumberOfDays Else i_CalculateNumberOfDays = i_NumberOfDays + (Int(((i_NumberOfDays + (7 - d_StartDate.DayOfWeek)) / 5)) * 2) End If MsgBox(DateAdd(DateInterval.Day, i_CalculateNumberOfDays, d_StartDate)) </code></pre> <p>Which I try to explain with the following piece of code:</p> <pre><code>''' create variables to begin with Dim d_StartDate as Date = "1-10-2009" Dim i_NumberOfDays as Integer = 5 ''' create var to store number of days to calculate with Dim i_AddNumberOfDays as Integer ''' check if amount of businessdays to add exceeds the ''' amount of businessdays left in the week of the startdate If i_NumberOfDays &gt; (5 - d_StartDate.DayOfWeek) Then ''' start by substracting days in week with the current day, ''' to calculate the remainder of days left in the current week i_AddNumberOfDays = 7 - d_StartDate.DayOfWeek ''' add the remainder of days in this week to the total ''' number of days we have to add to the date i_AddNumberOfDays += i_NumberOfDays ''' divide by 5, because we need to know how many ''' business weeks we are dealing with i_AddNumberOfDays = i_AddNumberOfDays / 5 ''' multiply the integer of current business weeks by 2 ''' those are the amount of days in the weekends we have ''' to add to the total i_AddNumberOfDays = Int(i_AddNumberOfDays) * 2 ''' add the number of days to the weekend days i_AddNumberOfDays += i_NumberOfDays Else ''' there are enough businessdays left in this week ''' to add the given amount of days i_AddNumberOfDays = i_NumberOfDays End If ''' this is the numberof dates to calculate with in DateAdd dim d_CalculatedDate as Date d_CalculatedDate = DateAdd(DateInterval.Day, i_AddNumberOfDays, d_StartDate) </code></pre> <p>Thanks in advance for your comments and input on this.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload