
Holidays and Weekends
I have an OrderDate and a ShipDate. I want to calculate the days between these
two, but would like to eliminate weekends and holidays. Anybody have a suggestion
on the best way to tackle this. I have access to VB6.0 and am using Access97'.
Thanks.

Re: Holidays and Weekends
Quixote,
Because there is no fixed and standard set of Universal Holidays, you
will have to set up a local list of the dates that you wish to regard as
holidays. You will then have to develop a local function that can count
the number of those dates which fall within any give period. This should
be fairly easy to implement.
As for weekend days, do you wnat to eliminate both Saturdays AND SUndays,
or just Sundays .I am assuming here that you are operating in the US  in
some other countries (in some mideast countries, the "weekend" is friday
and Saturday, for instance) that is not necessarily the case.
Arthur Wood
"quixote" <quixote15@excite.com> wrote:
>
>I have an OrderDate and a ShipDate. I want to calculate the days between
these
>two, but would like to eliminate weekends and holidays. Anybody have a suggestion
>on the best way to tackle this. I have access to VB6.0 and am using Access97'.
>Thanks.

Re: Holidays and Weekends
I have a table in the DB with all the Holidays in it. Also, weekends are Sat
and Sun. I am a novice programmer and am having trouble finding proprietary
functions or creating my own to tackle the following logic:
If a Holiday or WeekendDay Is Between OrderDate and ShipDate Then
Subtract the number of Holidays and number of WeekEndDays From
The total NumberOfDays
frustration ensues......Ain't life Grand!?....
"Arthur Wood" <wooda@saictrsc.com> wrote:
>
>Quixote,
> Because there is no fixed and standard set of Universal Holidays, you
>will have to set up a local list of the dates that you wish to regard as
>holidays. You will then have to develop a local function that can count
>the number of those dates which fall within any give period. This should
>be fairly easy to implement.
>
> As for weekend days, do you wnat to eliminate both Saturdays AND SUndays,
>or just Sundays .I am assuming here that you are operating in the US  in
>some other countries (in some mideast countries, the "weekend" is friday
>and Saturday, for instance) that is not necessarily the case.
>
>Arthur Wood
>
>
>"quixote" <quixote15@excite.com> wrote:
>>
>>I have an OrderDate and a ShipDate. I want to calculate the days between
>these
>>two, but would like to eliminate weekends and holidays. Anybody have a
suggestion
>>on the best way to tackle this. I have access to VB6.0 and am using Access97'.
>>Thanks.
>

Re: Holidays and Weekends
Quixote,
Do this is two steps:
1) "Select Count(*)as Holidays from Holidays where HolidayDate BETWEEN
#" & Orderdate "# AND #" & ShipDate &"#"
this query will return as a single value the number of Holidays in the
interval.
2) for the number of weekdays in the interval, use the builtin VB function
DateDiff:
dim WeekDays as Integer
WeekDays = DateDiff("W",Orderdate,ShipDate)
here the "w" tless the function to count weekdays!!!!
then you would subtract the number of Holidays returned in part 1 from the
Number of Weekdays in part 2.
Arthur Wood
"quixote" <quixote15@excite.com> wrote:
>
>I have a table in the DB with all the Holidays in it. Also, weekends are
Sat
>and Sun. I am a novice programmer and am having trouble finding proprietary
>functions or creating my own to tackle the following logic:
>
>If a Holiday or WeekendDay Is Between OrderDate and ShipDate Then
>Subtract the number of Holidays and number of WeekEndDays From
>The total NumberOfDays
>
>frustration ensues......Ain't life Grand!?....
>
>
>
>
>
>
>"Arthur Wood" <wooda@saictrsc.com> wrote:
>>
>>Quixote,
>> Because there is no fixed and standard set of Universal Holidays, you
>>will have to set up a local list of the dates that you wish to regard as
>>holidays. You will then have to develop a local function that can count
>>the number of those dates which fall within any give period. This should
>>be fairly easy to implement.
>>
>> As for weekend days, do you wnat to eliminate both Saturdays AND SUndays,
>>or just Sundays .I am assuming here that you are operating in the US 
in
>>some other countries (in some mideast countries, the "weekend" is friday
>>and Saturday, for instance) that is not necessarily the case.
>>
>>Arthur Wood
>>
>>
>>"quixote" <quixote15@excite.com> wrote:
>>>
>>>I have an OrderDate and a ShipDate. I want to calculate the days between
>>these
>>>two, but would like to eliminate weekends and holidays. Anybody have a
>suggestion
>>>on the best way to tackle this. I have access to VB6.0 and am using Access97'.
>>>Thanks.
>>
>

Re: Holidays and Weekends
Thank you!
"Arthur Wood" <wooda@saictrsc.com> wrote:
>
>Quixote,
> Do this is two steps:
>
> 1) "Select Count(*)as Holidays from Holidays where HolidayDate BETWEEN
>#" & Orderdate "# AND #" & ShipDate &"#"
>
> this query will return as a single value the number of Holidays in the
>interval.
>
> 2) for the number of weekdays in the interval, use the builtin VB function
>DateDiff:
>
> dim WeekDays as Integer
>
> WeekDays = DateDiff("W",Orderdate,ShipDate)
>
>here the "w" tless the function to count weekdays!!!!
>
>then you would subtract the number of Holidays returned in part 1 from the
>Number of Weekdays in part 2.
>
>Arthur Wood
>
>
>"quixote" <quixote15@excite.com> wrote:
>>
>>I have a table in the DB with all the Holidays in it. Also, weekends are
>Sat
>>and Sun. I am a novice programmer and am having trouble finding proprietary
>>functions or creating my own to tackle the following logic:
>>
>>If a Holiday or WeekendDay Is Between OrderDate and ShipDate Then
>>Subtract the number of Holidays and number of WeekEndDays From
>>The total NumberOfDays
>>
>>frustration ensues......Ain't life Grand!?....
>>
>>
>>
>>
>>
>>
>>"Arthur Wood" <wooda@saictrsc.com> wrote:
>>>
>>>Quixote,
>>> Because there is no fixed and standard set of Universal Holidays, you
>>>will have to set up a local list of the dates that you wish to regard
as
>>>holidays. You will then have to develop a local function that can count
>>>the number of those dates which fall within any give period. This should
>>>be fairly easy to implement.
>>>
>>> As for weekend days, do you wnat to eliminate both Saturdays AND SUndays,
>>>or just Sundays .I am assuming here that you are operating in the US 
>in
>>>some other countries (in some mideast countries, the "weekend" is friday
>>>and Saturday, for instance) that is not necessarily the case.
>>>
>>>Arthur Wood
>>>
>>>
>>>"quixote" <quixote15@excite.com> wrote:
>>>>
>>>>I have an OrderDate and a ShipDate. I want to calculate the days between
>>>these
>>>>two, but would like to eliminate weekends and holidays. Anybody have
a
>>suggestion
>>>>on the best way to tackle this. I have access to VB6.0 and am using Access97'.
>>>>Thanks.
>>>
>>
>

Re: Holidays and Weekends
> 2) for the number of weekdays in the interval, use the builtin VB
function
> DateDiff:
>
> dim WeekDays as Integer
>
> WeekDays = DateDiff("W",Orderdate,ShipDate)
>
> here the "w" tless the function to count weekdays!!!!
>
> then you would subtract the number of Holidays returned in part 1 from the
> Number of Weekdays in part 2.
That is not my understanding of the "w" argument. You don't get a count of
work days, but rather a count of the number of weeks between the two dates
(check the VB help files). As to the count of total work days between two
dates, consider this which Larry Serflaten once posted this answer to a
similar question:
Digging up a past response from Deja.com, Myrna Larson offered:
http://deja.com/getdoc.xp?AN=436942720
NumWeeks = (EndDate  StartDate) \ 7
WorkingDays = NumWeeks * 5
For D = (StartDate + NumWeeks * 7) To EndDate
If (Weekday(D) Mod 6) <> 1 Then WorkingDays = WorkingDays + 1
Next D
Rick

Re: Holidays and Weekends
Rick,
According to the DateDiff documentation, "w" gives the count of weekdays,
"ww" gives the count of weeks.
Arthur Wood
"Rick Rothstein" <rick_newsgroup@email.com> wrote:
>> 2) for the number of weekdays in the interval, use the builtin VB
>function
>> DateDiff:
>>
>> dim WeekDays as Integer
>>
>> WeekDays = DateDiff("W",Orderdate,ShipDate)
>>
>> here the "w" tless the function to count weekdays!!!!
>>
>> then you would subtract the number of Holidays returned in part 1 from
the
>> Number of Weekdays in part 2.
>
>That is not my understanding of the "w" argument. You don't get a count
of
>work days, but rather a count of the number of weeks between the two dates
>(check the VB help files). As to the count of total work days between two
>dates, consider this which Larry Serflaten once posted this answer to a
>similar question:
>
>Digging up a past response from Deja.com, Myrna Larson offered:
>http://deja.com/getdoc.xp?AN=436942720
>
>NumWeeks = (EndDate  StartDate) \ 7
>WorkingDays = NumWeeks * 5
>For D = (StartDate + NumWeeks * 7) To EndDate
> If (Weekday(D) Mod 6) <> 1 Then WorkingDays = WorkingDays + 1
>Next D
>
>Rick
>
>

Re: Holidays and Weekends
Would you agree that there is more than one weekday between 11/16/2000 and
11/24/2000? Go to the Immediate Window and type
Print DateDiff("W", #11/16/2000#, #11/24/2000#)
It will return 1 which is the number of weeks, not weekdays, between these
dates. If you check out the Remarks section of the help files for DateDiff
you will find this line, "When interval is Weekday ("w"), DateDiff returns
the number of weeks between the two dates." which is then followed by a
brief discussion as to the way in which "w" and "ww" differ.
The routine I posted (actually Larry Serflaten's code) will in fact count
actual weekdays and returns the correct answer of 7. Of course, quixote is
still on his/her own regarding the holidays.
Rick
"Arthur Wood" <wooda@saictrsc.com> wrote in message
news:3a13e38e$1@news.devx.com...
>
> Rick,
> According to the DateDiff documentation, "w" gives the count of
weekdays,
> "ww" gives the count of weeks.
>
> Arthur Wood
>
> "Rick Rothstein" <rick_newsgroup@email.com> wrote:
> >> 2) for the number of weekdays in the interval, use the builtin VB
> >function
> >> DateDiff:
> >>
> >> dim WeekDays as Integer
> >>
> >> WeekDays = DateDiff("W",Orderdate,ShipDate)
> >>
> >> here the "w" tless the function to count weekdays!!!!
> >>
> >> then you would subtract the number of Holidays returned in part 1 from
> the
> >> Number of Weekdays in part 2.
> >
> >That is not my understanding of the "w" argument. You don't get a count
> of
> >work days, but rather a count of the number of weeks between the two
dates
> >(check the VB help files). As to the count of total work days between two
> >dates, consider this which Larry Serflaten once posted this answer to a
> >similar question:
> >
> >Digging up a past response from Deja.com, Myrna Larson offered:
> >http://deja.com/getdoc.xp?AN=436942720
> >
> >NumWeeks = (EndDate  StartDate) \ 7
> >WorkingDays = NumWeeks * 5
> >For D = (StartDate + NumWeeks * 7) To EndDate
> > If (Weekday(D) Mod 6) <> 1 Then WorkingDays = WorkingDays + 1
> >Next D
> >
> >Rick
> >
> >
>

Re: Holidays and Weekends
Rick,
Sorry for the confusion. I didn't read all of the details about the use
of "w" and "ww". Foolish me, I took the term weekday at face value, and
assumed that it would return the count of the number of weekdays in the interval,
not the count of the number of occurences of THAT WEEKDAY in the interval.
I stand corrcted, and the code you supplied will in fact do what is required.
Arthur Wood
"Rick Rothstein" <rick_newsgroup@email.com> wrote:
>Would you agree that there is more than one weekday between 11/16/2000 and
>11/24/2000? Go to the Immediate Window and type
>
> Print DateDiff("W", #11/16/2000#, #11/24/2000#)
>
>It will return 1 which is the number of weeks, not weekdays, between these
>dates. If you check out the Remarks section of the help files for DateDiff
>you will find this line, "When interval is Weekday ("w"), DateDiff returns
>the number of weeks between the two dates." which is then followed by a
>brief discussion as to the way in which "w" and "ww" differ.
>
>The routine I posted (actually Larry Serflaten's code) will in fact count
>actual weekdays and returns the correct answer of 7. Of course, quixote
is
>still on his/her own regarding the holidays.
>
>Rick
>
>
>
>"Arthur Wood" <wooda@saictrsc.com> wrote in message
>news:3a13e38e$1@news.devx.com...
>>
>> Rick,
>> According to the DateDiff documentation, "w" gives the count of
>weekdays,
>> "ww" gives the count of weeks.
>>
>> Arthur Wood
>>
>> "Rick Rothstein" <rick_newsgroup@email.com> wrote:
>> >> 2) for the number of weekdays in the interval, use the builtin
VB
>> >function
>> >> DateDiff:
>> >>
>> >> dim WeekDays as Integer
>> >>
>> >> WeekDays = DateDiff("W",Orderdate,ShipDate)
>> >>
>> >> here the "w" tless the function to count weekdays!!!!
>> >>
>> >> then you would subtract the number of Holidays returned in part 1 from
>> the
>> >> Number of Weekdays in part 2.
>> >
>> >That is not my understanding of the "w" argument. You don't get a count
>> of
>> >work days, but rather a count of the number of weeks between the two
>dates
>> >(check the VB help files). As to the count of total work days between
two
>> >dates, consider this which Larry Serflaten once posted this answer to
a
>> >similar question:
>> >
>> >Digging up a past response from Deja.com, Myrna Larson offered:
>> >http://deja.com/getdoc.xp?AN=436942720
>> >
>> >NumWeeks = (EndDate  StartDate) \ 7
>> >WorkingDays = NumWeeks * 5
>> >For D = (StartDate + NumWeeks * 7) To EndDate
>> > If (Weekday(D) Mod 6) <> 1 Then WorkingDays = WorkingDays + 1
>> >Next D
>> >
>> >Rick
>> >
>> >
>>
>
>

Re: Holidays and Weekends
Don't feel bad, the usage is not an obvious one. Also, when I first
attempted to use this functionality, I too misinterpreted that option.
Rick
"Arthur Wood" <wooda@saictrsc.com> wrote in message
news:3a153a1d$1@news.devx.com...
>
> Rick,
> Sorry for the confusion. I didn't read all of the details about the
use
> of "w" and "ww". Foolish me, I took the term weekday at face value, and
> assumed that it would return the count of the number of weekdays in the
interval,
> not the count of the number of occurences of THAT WEEKDAY in the interval.
>
> I stand corrcted, and the code you supplied will in fact do what is
required.
>
> Arthur Wood
>
> "Rick Rothstein" <rick_newsgroup@email.com> wrote:
> >Would you agree that there is more than one weekday between 11/16/2000
and
> >11/24/2000? Go to the Immediate Window and type
> >
> > Print DateDiff("W", #11/16/2000#, #11/24/2000#)
> >
> >It will return 1 which is the number of weeks, not weekdays, between
these
> >dates. If you check out the Remarks section of the help files for
DateDiff
> >you will find this line, "When interval is Weekday ("w"), DateDiff
returns
> >the number of weeks between the two dates." which is then followed by a
> >brief discussion as to the way in which "w" and "ww" differ.
> >
> >The routine I posted (actually Larry Serflaten's code) will in fact count
> >actual weekdays and returns the correct answer of 7. Of course, quixote
> is
> >still on his/her own regarding the holidays.
> >
> >Rick
> >
> >
> >
> >"Arthur Wood" <wooda@saictrsc.com> wrote in message
> >news:3a13e38e$1@news.devx.com...
> >>
> >> Rick,
> >> According to the DateDiff documentation, "w" gives the count of
> >weekdays,
> >> "ww" gives the count of weeks.
> >>
> >> Arthur Wood
> >>
> >> "Rick Rothstein" <rick_newsgroup@email.com> wrote:
> >> >> 2) for the number of weekdays in the interval, use the builtin
> VB
> >> >function
> >> >> DateDiff:
> >> >>
> >> >> dim WeekDays as Integer
> >> >>
> >> >> WeekDays = DateDiff("W",Orderdate,ShipDate)
> >> >>
> >> >> here the "w" tless the function to count weekdays!!!!
> >> >>
> >> >> then you would subtract the number of Holidays returned in part 1
from
> >> the
> >> >> Number of Weekdays in part 2.
> >> >
> >> >That is not my understanding of the "w" argument. You don't get a
count
> >> of
> >> >work days, but rather a count of the number of weeks between the two
> >dates
> >> >(check the VB help files). As to the count of total work days between
> two
> >> >dates, consider this which Larry Serflaten once posted this answer to
> a
> >> >similar question:
> >> >
> >> >Digging up a past response from Deja.com, Myrna Larson offered:
> >> >http://deja.com/getdoc.xp?AN=436942720
> >> >
> >> >NumWeeks = (EndDate  StartDate) \ 7
> >> >WorkingDays = NumWeeks * 5
> >> >For D = (StartDate + NumWeeks * 7) To EndDate
> >> > If (Weekday(D) Mod 6) <> 1 Then WorkingDays = WorkingDays + 1
> >> >Next D
> >> >
> >> >Rick
> >> >
> >> >
> >>
> >
> >
>
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

Development Centers
 Android Development Center
 Cloud Development Project Center
 HTML5 Development Center
 Windows Mobile Development Center
