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.
Printable View
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.
Quick google, gives the following:
http://www.experts-exchange.com/Prog..._23461938.html
(scroll to the bottom)
Google gave his as first result:
Try this:
Code: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
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.Quote:
Originally Posted by nzhmz
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?Quote:
Originally Posted by 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.
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.
Code: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
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
Oh, I didn't realize that we've just resurrected a 2+ year old thread :)
Does this work? If vAgreedTRT is the number of days, and vDateReceived is the date we are starting with it doesn't!Quote:
Originally Posted by DavidAllanJames
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.Quote:
Originally Posted by stanav
My one-liner :rolleyes:
Code:
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
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!
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.
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.
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.
Sounds like we are doing the same thing. I didn't write the version I'm using. I got it somewhere off the internet.
Code: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