Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Date Diff between 2 dates after ignoring weekends 2
- Thread starter pankajv
- Start date
Oh, and I have already mentioned that strongm's code also seems to produce an error as well as yours, as well as mine did and still may.
I know there is alot to consider here, with these types of functions, and therefore alot of testing is always needed, also user different settings.
Therefore, we should all appreciate it when others spot (possible) errors in one's code.
[/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!
I know there is alot to consider here, with these types of functions, and therefore alot of testing is always needed, also user different settings.
Therefore, we should all appreciate it when others spot (possible) errors in one's code.
[/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!
MichaelRed
Programmer
From an earlier post in THIS THREAD:
strongm (MIS) Oct 25, 2002
Here's the thread: Thread222-361893
Where strongm posted a routine, you posted some information and at least a part of the process and I referenced the FAQ which I posted quite a while back.
Of course, with these latest changes, I believe Your routine returns the same value as the others.
MichaelRed
m.red@att.net
There is never time to do it right but there is always time to do it over
strongm (MIS) Oct 25, 2002
Here's the thread: Thread222-361893
Where strongm posted a routine, you posted some information and at least a part of the process and I referenced the FAQ which I posted quite a while back.
Of course, with these latest changes, I believe Your routine returns the same value as the others.
MichaelRed
m.red@att.net
There is never time to do it right but there is always time to do it over
Error? ERROR? Oh, er, um...OK, try the following minor modification...
[tt]
' Assumes EndDate>= StartDate
Private Function CountWeekdaysBetweenDates(Startdate As Date, EndDate As Date) As Long
If Weekday(Startdate, vbMonday) > 5 Then Startdate = DateAdd("d", 3 - Weekday(Startdate, vbSaturday), Startdate)
If Weekday(EndDate, vbMonday) > 5 Then EndDate = DateAdd("d", -Weekday(EndDate, vbSaturday), EndDate)
CountWeekdaysBetweenDates = DateDiff("d", Startdate, EndDate) - 2 * (DateDiff("ww", Startdate, EndDate))
If CountWeekdaysBetweenDates < 0 Then CountWeekdaysBetweenDates = 0
End Function
[/tt]
But remember, this bit of code was only really a bit of fun to see how compressed we could make the code. it sure doesn't deal with issues like holidays, or different 'weekends', etc.
[tt]
' Assumes EndDate>= StartDate
Private Function CountWeekdaysBetweenDates(Startdate As Date, EndDate As Date) As Long
If Weekday(Startdate, vbMonday) > 5 Then Startdate = DateAdd("d", 3 - Weekday(Startdate, vbSaturday), Startdate)
If Weekday(EndDate, vbMonday) > 5 Then EndDate = DateAdd("d", -Weekday(EndDate, vbSaturday), EndDate)
CountWeekdaysBetweenDates = DateDiff("d", Startdate, EndDate) - 2 * (DateDiff("ww", Startdate, EndDate))
If CountWeekdaysBetweenDates < 0 Then CountWeekdaysBetweenDates = 0
End Function
[/tt]
But remember, this bit of code was only really a bit of fun to see how compressed we could make the code. it sure doesn't deal with issues like holidays, or different 'weekends', etc.
MichaelRed
Programmer
Actually, the FAQ code is intended to work that way, as it needs to ALWAYS return the number of "business days", it includes the 'ending day'.
MichaelRed
m.red@att.net
There is never time to do it right but there is always time to do it over
MichaelRed
m.red@att.net
There is never time to do it right but there is always time to do it over
Then,
DeltaDays("10/24/02","10/25/02"
Thurs to Fri
should return 2, not 1?
And,
DeltaDays("10/19/02","10/21/02" Sat. to Monday
should return 1, and not 2 (on my system)?
And,
DeltaDays("10/19/02","10/20/02" Sat. to Monday
should return 0, and not 1 (on my system)?
I think because where "Case Is = 6 " it should read "Case Is = 7", instead.
And strongm's code doesn't count the 1st day, as the DeltaDays function does. This is a matter of what is desired to be returned, but still shows that the functions return 2 different results.
But, question is, are we counting workdays or time spans?
If workdays, then the start day should be counted.
If time spans, then mine, and strongm's function should maybe result in 3 for tues to friday, but 4 for tues to sat?
Now I'm not sure which should be considered correct. I think the function should include a parameter to allow the user to decide whether to include the start date in the results or not.
In any case, tues to friday should return 3 (4) start date included(not included)
and tues to Sat should return 4 (4)start date included(not included)
And I still see that there may be an error in my code. (may have mis-lead myself because of the above thoughts)
And, therefore, I think all 3 examples are still faulty in one way or another.
[/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!
DeltaDays("10/24/02","10/25/02"
Thurs to Frishould return 2, not 1?
And,
DeltaDays("10/19/02","10/21/02"
Sat. to Mondayshould return 1, and not 2 (on my system)?
And,
DeltaDays("10/19/02","10/20/02"
Sat. to Mondayshould return 0, and not 1 (on my system)?
I think because where "Case Is = 6 " it should read "Case Is = 7", instead.
And strongm's code doesn't count the 1st day, as the DeltaDays function does. This is a matter of what is desired to be returned, but still shows that the functions return 2 different results.
But, question is, are we counting workdays or time spans?
If workdays, then the start day should be counted.
If time spans, then mine, and strongm's function should maybe result in 3 for tues to friday, but 4 for tues to sat?
Now I'm not sure which should be considered correct. I think the function should include a parameter to allow the user to decide whether to include the start date in the results or not.
In any case, tues to friday should return 3 (4) start date included(not included)
and tues to Sat should return 4 (4)start date included(not included)
And I still see that there may be an error in my code. (may have mis-lead myself because of the above thoughts)
And, therefore, I think all 3 examples are still faulty in one way or another.
[/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!
Ok, wait a minute:
>I think because where "Case Is = 6 " it should read "Case Is = 7", instead.
This is a wrong assumption.
I'll be right back....
[/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!
>I think because where "Case Is = 6 " it should read "Case Is = 7", instead.
This is a wrong assumption.
I'll be right back....
[/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!
MichaelRed
Programmer
Hmmmmmmmmmm,
I think I'll leave now. I DID get you to (at least implicitly) admit that yours is not the first of these wheels - regardless of the 'roundness' of any of them. Here, the 'beauty' is clearly in the eye of the beholder. I have ALWAYS had some reservations on which "days" should be counted in this routine (esp. start and end), but did it 'to spec' - not to my own concept.
I do think that switch(es) to control some of the aspects would be useful, but they then impose the burden of needing educated users - and that lot is in short supply.
MichaelRed
m.red@att.net
There is never time to do it right but there is always time to do it over
I think I'll leave now. I DID get you to (at least implicitly) admit that yours is not the first of these wheels - regardless of the 'roundness' of any of them. Here, the 'beauty' is clearly in the eye of the beholder. I have ALWAYS had some reservations on which "days" should be counted in this routine (esp. start and end), but did it 'to spec' - not to my own concept.
I do think that switch(es) to control some of the aspects would be useful, but they then impose the burden of needing educated users - and that lot is in short supply.
MichaelRed
m.red@att.net
There is never time to do it right but there is always time to do it over
>>I DID get you to (at least implicitly) admit that yours is not the first of these wheels...
No, I never said that, and I don't think that I needed to admit anything here in that fashion, because it was you who threw that, among a few other things, at me in the first place.
I am only offering different method, and a method that works under different circumstances, more flexible, different environments.
[/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!
No, I never said that, and I don't think that I needed to admit anything here in that fashion, because it was you who threw that, among a few other things, at me in the first place.
I am only offering different method, and a method that works under different circumstances, more flexible, different environments.
[/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!
For the sake of anyone who has decided to use the code which I have posted, I will post the corrected code below in
the next post.
The added features give the user the possibility to:
- use a startdate greater than the end date;
- determine how many work days in a week there are;
- determine if the holiday function returns a value based only on work days
- determine if the holiday function is taken into consideration at all;
- determine the 1st work day;
- determine if the first or last days are included in the total count;
- no need to pass the end date if it is todays date;
- use the functions independently of each other;
- pass the dates as strings or date variables;
- offer a method that is alot faster than looping through each day, with determining the raw number of business days (30+ times faster).
- easily change the holiday function to get data from an array;
The user could even pass:
CountWeekDays_pFlng("01/01/02", iDaysInWorkWeek:=7)
and get the day of the year, as can be done with the DataPart function.
The parameters have changed and the variable types for the start and end dates as well (and passing them by val so a string or a Date type variable can be passsed).
(I have left out the function to pull the records from the MDB as it has remained the same)
If anyone finds any problems, then please feel free to post your findings. [/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!
the next post.
The added features give the user the possibility to:
- use a startdate greater than the end date;
- determine how many work days in a week there are;
- determine if the holiday function returns a value based only on work days
- determine if the holiday function is taken into consideration at all;
- determine the 1st work day;
- determine if the first or last days are included in the total count;
- no need to pass the end date if it is todays date;
- use the functions independently of each other;
- pass the dates as strings or date variables;
- offer a method that is alot faster than looping through each day, with determining the raw number of business days (30+ times faster).
- easily change the holiday function to get data from an array;
The user could even pass:
CountWeekDays_pFlng("01/01/02", iDaysInWorkWeek:=7)
and get the day of the year, as can be done with the DataPart function.
The parameters have changed and the variable types for the start and end dates as well (and passing them by val so a string or a Date type variable can be passsed).
(I have left out the function to pull the records from the MDB as it has remained the same)
If anyone finds any problems, then please feel free to post your findings. [/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!
Public Function CountWeekDays_pFlng(ByVal sStartDate As String, Optional ByVal sEndDate As String, _
Optional ByVal bInclStartDateInCount As Boolean = False, _
Optional ByVal bInclEndDateInCount As Boolean = True, _
Optional ByVal bCalcHolidays As Boolean = True, _
Optional ByVal lFirstWorkDay As VbDayOfWeek = vbMonday, _
Optional iDaysInWorkWeek As Integer = 5) As Long
Dim lWeeks As Long
Dim lWeekDays As Long
Dim dtCurrentDate As Date
Dim dtEndDate As Date
Dim dtStartDate As Date
Dim iSgn As Integer
If Not IsDate(sEndDate) Then sEndDate = Date
If Not IsDate(sStartDate) Then sStartDate = Date
dtEndDate = CDate(sEndDate)
dtStartDate = CDate(sStartDate)
iSgn = Sgn(dtEndDate - dtStartDate)
If iSgn = 0 Then
iSgn = 1
ElseIf iSgn = -1 Then
dtCurrentDate = dtStartDate
dtStartDate = dtEndDate
dtEndDate = dtCurrentDate
End If
lWeeks = DateDiff("w", dtStartDate, dtEndDate, lFirstWorkDay, vbFirstJan1)
lWeekDays = lWeeks * iDaysInWorkWeek
dtCurrentDate = dtStartDate + (lWeeks * 7)
If Not bInclStartDateInCount Then dtCurrentDate = dtCurrentDate + 1
If Not bInclEndDateInCount Then dtEndDate = dtEndDate - 1
Do Until dtCurrentDate > dtEndDate
lWeekDays = lWeekDays + (Abs(Weekday(dtCurrentDate, lFirstWorkDay) < (iDaysInWorkWeek + 1)))
dtCurrentDate = dtCurrentDate + 1
Loop
If bCalcHolidays Then lWeekDays = lWeekDays - CountHolidaysInRange_pFlng(dtStartDate, dtEndDate, bWeekDaysOnly:=True, _
bInclStartDateInCount:=bInclStartDateInCount, bInclEndDateInCount:=bInclEndDateInCount, _
lFirstWorkDay:=lFirstWorkDay, iDaysInWorkWeek:=iDaysInWorkWeek)
CountWeekDays_pFlng = lWeekDays * iSgn
End Function
==========================================
Private Function CountHolidaysInRange_pFlng(Optional ByVal sStartDate As String, Optional ByVal sEndDate As String, _
Optional ByVal bWeekDaysOnly As Boolean = True, _
Optional ByVal bInclStartDateInCount As Boolean = False, _
Optional ByVal bInclEndDateInCount As Boolean = True, _
Optional ByVal lFirstWorkDay As VbDayOfWeek = vbMonday, _
Optional iDaysInWorkWeek As Integer = 5, _
Optional sList As Variant) As Long
Dim dtItem As Variant
Dim dtCurrentDate As Date
Dim dtEndDate As Date
Dim dtStartDate As Date
Dim lHolidays As Long
Dim bAddDay As Boolean
If Not IsDate(sEndDate) Then sEndDate = Date
If Not IsDate(sStartDate) Then sStartDate = Date
dtEndDate = CDate(sEndDate)
dtStartDate = CDate(sStartDate)
If dtEndDate < dtStartDate Then
'Flip the dates
dtCurrentDate = dtStartDate
dtStartDate = dtEndDate
dtEndDate = dtCurrentDate
End If
'Get list of Holidays between the date range
If IsMissing(sList) Then sList = GetHolidaysInRange_pFv(dtStartDate, dtEndDate)
If Not IsEmpty(sList) Then
If bWeekDaysOnly Then
'Check if the date is a weekday or not
For Each dtItem In sList
bAddDay = (dtItem = dtStartDate And bInclStartDateInCount) _
Or (dtItem = dtEndDate And bInclEndDateInCount) _
Or (dtItem <> dtStartDate And dtItem <> dtEndDate)
If bAddDay Then lHolidays = lHolidays + Abs(Weekday(dtItem, lFirstWorkDay) < (iDaysInWorkWeek + 1))
Next dtItem
Else
lHolidays = UBound(sList) - LBound(sList)
End If
End If
CountHolidaysInRange_pFlng = lHolidays
End Function
[/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!
- Status
