Adding working days to date

[nzhmz]

Hi,
In my project, i need to add days to a given date. but when adding the days it should not have saturdays and sundays.

can anyone help me to find a solution for this?

thanks in advance.

[enrico1982]

Quick google, gives the following:

http://www.experts-exchange.com/Prog..._23461938.html

(scroll to the bottom)

[Dnereb]

Google gave his as first result:
google

[stanav]

Try this:

 Private Function AddWorkDays(ByVal startDate As Date, ByVal workDays As Integer) As Date
        Dim endDate As Date = startDate
        Dim n As Integer = 0
        If workDays > 0 Then
            n = 1
        ElseIf workDays < 0 Then
            n = -1
        End If
        If n <> 0 Then
            For i = 1 To Math.Abs(workDays)
                endDate = endDate.AddDays(n)
                While (endDate.DayOfWeek = DayOfWeek.Saturday OrElse endDate.DayOfWeek = DayOfWeek.Sunday)
                    endDate = endDate.AddDays(n)
                End While
            Next
        End If
        Return endDate
    End Function

[DavidAllanJames]

Hi,
In my project, i need to add days to a given date. but when adding the days it should not have saturdays and sundays.

can anyone help me to find a solution for this?

thanks in advance.

Searched the Net - and other than do...while or for...next loops neither of which are elegant, I couldn't find anything which worked semi-formulaic; so i wrote one.

Public Function CalcDueDate(ByVal vDateReceived As Variant, ByVal vAgreedTRT As Variant) As Variant
' David Allan James (c) 2013 Free Distribution
Dim iDayOfWeek As Integer
Dim iDiffDaysFromMonday As Integer

CalcDueDate = Null
If IsNull(vDateReceived) Or IsNull(vAgreedTRT) Then
Exit Function
End If

iDayOfWeek = DatePart("w", vDateReceived)

' if the Date Received is a Sat / Sun, assume Mon
If iDayOfWeek = 1 Then ' Sunday
vDateReceived = DateAdd("d", 1, vDateReceived)
iDiffDaysFromMonday = 0
ElseIf iDayOfWeek = 7 Then ' Saturday
vDateReceived = DateAdd("d", 2, vDateReceived)
iDiffDaysFromMonday = 0
Else
iDiffDaysFromMonday = iDayOfWeek - 2
End If

vAgreedTRT = vAgreedTRT + iDiffDaysFromMonday
vDateReceived = DateAdd("d", iDiffDaysFromMonday * -1, vDateReceived)

' This calculation only works if going from Monday. So all the tweeking above of TRT & Date Received
CalcDueDate = DateAdd("d", vAgreedTRT Mod 5, _
DateAdd("ww", Int(vAgreedTRT / 5), vDateReceived))
End Function

[stanav]

Searched the Net - and other than do...while or for...next loops neither of which are elegant, I couldn't find anything which worked semi-formulaic; so i wrote one.

Public Function CalcDueDate(ByVal vDateReceived As Variant, ByVal vAgreedTRT As Variant) As Variant
' David Allan James (c) 2013 Free Distribution
Dim iDayOfWeek As Integer
Dim iDiffDaysFromMonday As Integer

CalcDueDate = Null
If IsNull(vDateReceived) Or IsNull(vAgreedTRT) Then
Exit Function
End If

iDayOfWeek = DatePart("w", vDateReceived)

' if the Date Received is a Sat / Sun, assume Mon
If iDayOfWeek = 1 Then ' Sunday
vDateReceived = DateAdd("d", 1, vDateReceived)
iDiffDaysFromMonday = 0
ElseIf iDayOfWeek = 7 Then ' Saturday
vDateReceived = DateAdd("d", 2, vDateReceived)
iDiffDaysFromMonday = 0
Else
iDiffDaysFromMonday = iDayOfWeek - 2
End If

vAgreedTRT = vAgreedTRT + iDiffDaysFromMonday
vDateReceived = DateAdd("d", iDiffDaysFromMonday * -1, vDateReceived)

' This calculation only works if going from Monday. So all the tweeking above of TRT & Date Received
CalcDueDate = DateAdd("d", vAgreedTRT Mod 5, _
DateAdd("ww", Int(vAgreedTRT / 5), vDateReceived))
End Function

Set aside the fact that this is NOT vb.net code, how is it any more elegant than using a loop?

[DavidAllanJames]

Its eligant as in it is not dependant upon loops executing - all of which take time.
It is VBA not VB.Net. Im sure anyone can convert the underlying code from one to the other.
I have used this code in 7 applications now with various tweeks.
Speeded up SQL code logic too - as I also easily converted it to SQL SProc too.

[TysonLPrice]

Just another link but it looks promising:

http://www.codeproject.com/Articles/...Days-to-a-Date

I just tested it and it seems to work OK. Very similar to what is already posted.

Public Function CalculateTenBusinessDaysFromInputDate(ByVal StartDate As Date) As Date
        'This simply adds at least 2 full weeks to the start date.

        Select Case StartDate.DayOfWeek

            Case DayOfWeek.Sunday
                'if the start date is not a sunday you need to add 
                '1 day to push it to a monday that is why the number is 15.
                Return StartDate.AddDays(15)
            Case DayOfWeek.Monday, DayOfWeek.Tuesday, _
            DayOfWeek.Wednesday, DayOfWeek.Thursday, DayOfWeek.Friday
                'if the start date is any other day then just add 14 days to the start date.
                Return StartDate.AddDays(14)
            Case DayOfWeek.Saturday
                'if the start date is on a Saturday you need to add 
                '2 days to push it to a monday that is why the number is 16.
                Return StartDate.AddDays(16)
            Case Else
                Return StartDate

        End Select

    End Function

[DavidAllanJames]

Yep... For...Next Loop... Keep trying guys; you are not going to find a faster more eligant solution than basically a 1 liner:
CalcDueDate = DateAdd("d", vAgreedTRT Mod 5, _
DateAdd("ww", Int(vAgreedTRT / 5), vDateReceived))
But keep trying... You may get there!
x

[stanav]

Oh, I didn't realize that we've just resurrected a 2+ year old thread

[dbasnett]

Yep... For...Next Loop... Keep trying guys; you are not going to find a faster more eligant solution than basically a 1 liner:
CalcDueDate = DateAdd("d", vAgreedTRT Mod 5, _
DateAdd("ww", Int(vAgreedTRT / 5), vDateReceived))
But keep trying... You may get there!
x

Does this work? If vAgreedTRT is the number of days, and vDateReceived is the date we are starting with it doesn't!

[stanav]

That is only part of his code... The full code is in post#5, which in no way resembles a single line.... Does it work? I don't know... but I take his words that it does.

[dbasnett]

That is only part of his code... The full code is in post#5, which in no way resembles a single line.... Does it work? I don't know... but I take his words that it does.

I guess it was the reference to it being a 'one-liner' that got me.

My one-liner

    Private Function addWorkingDays(d As DateTime, daysToAdd As Integer) As DateTime
        If daysToAdd <= 0 Then Throw New Exception("foo")
        Dim rv As DateTime = d
        Dim ctDays As Integer = daysToAdd
        'force date to a Monday
        'note: a Sat or Sun start date + 1 day = a Monday
        Do While rv.DayOfWeek <> DayOfWeek.Monday AndAlso ctDays > 0
            rv = rv.AddDays(1)
            ctDays -= 1
            If rv.DayOfWeek = DayOfWeek.Saturday Then
                rv = rv.AddDays(2)
            ElseIf rv.DayOfWeek = DayOfWeek.Sunday Then
                rv = rv.AddDays(1)
            End If
        Loop
        Dim wk As Integer = ctDays \ 5 'how many business weeks
        ctDays -= wk * 5 'substract from day count
        ctDays += wk * 7 'convert business weeks to calendar days
        rv = rv.AddDays(ctDays)
        Return rv
    End Function

[DavidAllanJames]

Like your alternative dbasnett! you calculate the number of days to add in terms of weeks (7, 14, 21) where as my mod 5 does the same thing in terms of weeks (1,2,3); actually think yours is faster as you dont have to use the inbuilt weeks function i have to use. Nice!

[DavidAllanJames]

altered my "1 liner" as per input from dbasnett:-
CalcDueDate = DateAdd("d", vAgreedTRT Mod 5 + (Int(vAgreedTRT / 5) * 7), vDateReceived)
Much faster than my original use of adding weeks.

[TysonLPrice]

At the company I work at we have processes that don't count weekends and holidays. We do that by maintaining a company holiday table. It's in SQL but I can post the logic. Usually when someone cares about weekdays, holidays are also part of the equation.

[DavidAllanJames]

i have done the same in my SQL version. - have a bank holiday table. and have this routine recursivly call itself passing in the number of bank hols...
Process is thus...
Initialy set the no. of bank hols parameter to 0... and
what i do then is once i have calcuated the end date, calculate the number of working day, bank holiday dates between the start and end dates.
Note i say working day, bank holiday dates. I discount bank holidays which are in error and are actually Sat / Sun....
Then, when i know the number of bank hols, I check to see if it has changed from previous itteration, if it has, the routine calls itself passing in the new bank holidays no. of days.
Inside the routine, it just adds this value to the original TRT days to calculate a new end date.
When the start date / end date generates the same no. of bank holiday days as the previous itteration you know the end date is correct and the process can complete.

[TysonLPrice]

Sounds like we are doing the same thing. I didn't write the version I'm using. I got it somewhere off the internet.

create FUNCTION [dbo].[CalculateBusinessDays](@startdate datetime, @days int)
   RETURNS datetime
AS
BEGIN
--declare a counter to keep track of how many days are passing
declare @counter int

/*
Check your business rules.  If 4 business days means you 
count starting tomorrow, set counter to 1.  If you start 
counting today, set counter to 0
*/
set @counter = 1

--declare a variable to hold the ending date
declare @enddate datetime

--set the end date to the start date.  we'll be 
-- incrementing it for each passing business day
set @enddate = @startdate

/*
Start your loop.
While your counter (which was set to 1), is less than 
or equal to the number of business days increment your 
end date
*/
WHILE @counter <= @days

BEGIN

--for each day, we'll add one to the end date
set @enddate = DATEADD(dd, 1, @enddate)

   --If the day is between 2 and 6 (meaning it's a week
   --day and the day is not in the holiday table, we'll 
   --increment the counter
   IF (DATEPART(dw, @enddate) between 2 and 6) AND 
      (@enddate not in 
          (
           select HolidayDate 
           from refCMIHoliday 
           where datepart(yyyy,Holidaydate) = datepart(yyyy,@enddate)
         )
       )
   BEGIN
      set @counter = @counter + 1
   END

--end the while loop
END

--return the end date
RETURN @enddate


--end the function
END