count dates 2

[xerife007]

hi!
Visual Basic forum

Hi need to count two dates ex: 12-12-2002 / 15-12-2002 / totdays=1 day (because there is sturday an sunday in midle)
At the end i need to count only the day's of the week and exclution of suturday and sunday.

thank you

 

[nigz]

Use the DateDiff function - something like this:

NoOfWeekDays = DateDiff("w",CDate(12-12-02),CDate(15-12-02))

 

[sunaj]

See

http://216.26.168.92/vbsquare/tips/tip393.html

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[CCLINT]

nigz: that will only return the number of weeks....not weekdays.

The below is similar to the function that sunaj pointed out, but allows the first day of the week to be passed as a parameter. One could also get user input in order to find out what is determined to be the first day of the week in their region.
Additionally, another parameter could built into and passed to the function in order to allow the user to define how many works days are in a week, and which days are not work days (some countries/companies have 6 or 7 working days a week; some countries/companies may have 2 separate weekend days such as Sunday and Wednesday)

Function CountWeekDays(ByRef dtStartDate As Date, ByRef dtEndDate As Date, _
Optional ByVal lFirstDayOfWeek As VbDayOfWeek = vbMonday) As Long
Dim lWeekDays As Long
Dim dtCurrentDate As Date

dtCurrentDate = dtStartDate
Do Until dtCurrentDate > dtEndDate
lWeekDays = lWeekDays + Abs(Weekday(dtCurrentDate, lFirstDayOfWeek) < 6)
dtCurrentDate = dtCurrentDate + 1
Loop
CountWeekDays = lWeekDays

End Function [/b][/i][/u]*******************************************************
_{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!}

 

[nigz]

<snip>
nigz: that will only return the number of weeks....not weekdays.
</snip>
from MSDN:
Setting Description
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second


But if that's an error then how about:

NoOfWeekDays = DateDiff(&quot;w&quot;,CDate(12-12-02),CDate(15-12-02))* 5

 

[Schweiger]

CCLINT:
To avoid to much loops I would like to count the weeks and start of the last date to loop only for a max of 6 times. Would the following code do the job?

Function CountWeekDays(ByRef dtStartDate As Date, ByRef dtEndDate As Date, _
                        Optional ByVal lFirstDayOfWeek As VbDayOfWeek = vbMonday) As Long
    Dim lWeekDays As Long
    Dim dtCurrentDate As Date

    lWeekDays = (dtEndDate - dtStartDate) \ 7
    dtCurrentDate = dtStartDate + (lWeekDays * 7)
    lWeekDays = lWeekDays * 5
    
    Do Until dtCurrentDate > dtEndDate
        lWeekDays = lWeekDays + Abs(Weekday(dtCurrentDate, lFirstDayOfWeek) < 6)
        dtCurrentDate = dtCurrentDate + 1
    Loop
    CountWeekDays = lWeekDays

End Function

 

[johnwm]

nigz,

From your page on MSDN: (my italics)


To calculate the number of days between date1 and date2, you can use either Day of year (&quot;y&quot or Day (&quot;d&quot. When interval is Weekday (&quot;w&quot, DateDiff returns the number of weeks between the two dates. If date1 falls on a Monday, DateDiff counts the number of Mondays until date2. It counts date2 but not date1. If interval is Week (&quot;ww&quot, however, the DateDiff function returns the number of calendar weeks between the two dates. It counts the number of Sundays between date1 and date2. DateDiff counts date2 if it falls on a Sunday; but it doesn't count date1, even if it does fall on a Sunday.

Let me know if this helps
________________________________________________________________
If you are worried about how to post, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'

 

[CCLINT]

nigz:
Multiplying the result by 5 will also not work as the date diff function only considers date intervals (if the 1st day of the week is sunday, and I use the dates 21 and 29 sept as the start and end date, you will get 2 as an answer even though only 8 days have past and only 5 working days between these dates)

Schweiger: of course that will also work


[/b][/i][/u]*******************************************************
_{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!}

 

[nigz]

OK I stand corrected the * 5 thing was not an entirely serious suggestion - N

 

[CajunCenturion]

I'm still working on my first cup of coffee, but how about this approach.

Get the Day of Week for Date 1
Get the Day of Week for Date 2

Subtract the proper number of days from the day of week that is later, so that both dates are now the same day of week.
Ex:
If Date1 = Monday and Date2 = Wednesday,
then Offset = +2, and subtract 2 days from Date2. (offset is positive since you're reducing the the actual interval by moving date2 closer to date1 - offset will be added back in later)

If Date1 = Friday and Date2 = Tuesday, then Offset = -3 and substract 3 days from Date1 (offset is negative because you're increasing the real interval by move date1 further away from date2 - again the offset will be added back in later)

Now use the DateDiff function to calcuate the number of weeks between the two adjusted dates, multiply by 5, and then add back in the adjustment offset. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[MichaelRed]

Since you will end up doing the holidays as well, might as well just go directly to the faq181-261


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[CCLINT]

In order to calculate negative days (EndDate is less than StartDate), a change needs to be made to my code:

Function CountWeekDays(ByVal dtStartDate As Date, ByVal dtEndDate As Date, _
Optional ByVal lFirstDayOfWeek As VbDayOfWeek = vbMonday) As Long
Dim lWeekDays As Long
Dim dtCurrentDate As Date
Dim iNeg As Integer

If dtEndDate < dtStartDate Then
iNeg = -1
dtCurrentDate = dtStartDate
dtStartDate = dtEndDate
dtEndDate = dtCurrentDate
End If

lWeekDays = (dtEndDate - dtStartDate) \ 7
dtCurrentDate = dtStartDate + (lWeekDays * 7)
lWeekDays = lWeekDays * 5

'dtCurrentDate = dtStartDate
Do Until dtCurrentDate > dtEndDate
lWeekDays = lWeekDays + Abs(Weekday(dtCurrentDate, lFirstDayOfWeek) < 6)
dtCurrentDate = dtCurrentDate + 1
Loop
CountWeekDays = lWeekDays * iNeg

End Function


CajunCenturion, did you mean something like this:

Function CountWeekDays2(ByVal dtStartDate As Date, ByVal dtEndDate As Date, _
Optional ByVal lFirstDayOfWeek As VbDayOfWeek = vbMonday) As Long
Dim lWeekDays As Long
Dim lWeekStart As Integer
Dim lWeekEnd As Integer

lWeekStart = Weekday(dtStartDate, vbMonday)
If lWeekStart > vbFriday Then lWeekStart = vbFriday
lWeekEnd = Weekday(dtEndDate, vbMonday)
If lWeekEnd > vbFriday Then lWeekEnd = vbFriday

dtStartDate = dtStartDate - lWeekStart + 1
dtEndDate = dtEndDate - lWeekEnd + 1

lWeekDays = DateDiff(&quot;w&quot;, dtStartDate, dtEndDate, vbMonday) * 5

lWeekDays = lWeekDays - lWeekStart + lWeekEnd

CountWeekDays2 = lWeekDays

End Function

Both return equal values (negative or positive).
Maybe you have the time to check the performance of the two?



In order to check holidays with these, an array of the holiday dates could be past and looped through checking if the holiday falls between the start and end dates, and if so, check if those holidays are on a weekend and keep a count of the matches - 6 additional lines of code - which I will post later. The only problem with holidays, is that the dates are different year to year and you would have to be careful not to have a date range that goes beyond the holiday dates passed, other than that, a simple array and the check in these subs will work fine.
MichaelRed's code may be easier to understand and work with though. [/b][/i][/u]*******************************************************
_{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!}

 

[tbuch]

I have created a control which contains 2 DTP's, one for the start date & one for the end date. You simply refer to the controls &quot;weekdays&quot; property to get the number of weekdays between the two selected dates. Let me know if you need it.

 

[strongm]

And here's yet another alternative:
[tt]
' Assumes EndDate>= StartDate
Private Function CountWeekdaysBetweenDates(Startdate As Date, EndDate As Date) As Long
If Weekday(Startdate, vbMonday) > 5 Then Startdate = DateAdd(&quot;d&quot;, 3 - Weekday(Startdate, vbSaturday), Startdate)
If Weekday(EndDate, vbMonday) > 5 Then EndDate = DateAdd(&quot;d&quot;, -Weekday(EndDate, vbSaturday), EndDate)
CountWeekdaysBetweenDates = DateDiff(&quot;d&quot;, Startdate, EndDate) - 2 * (DateDiff(&quot;ww&quot;, Startdate, EndDate))
End Function

 

[CCLINT]

Wow! How long did you have to sit on that one?! (before it hatched) [/b][/i][/u]*******************************************************
_{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!}

 

[strongm]

Not too long