Holidays and Weekends

[quixote]

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.

[Arthur Wood]

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 mid-east 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.

[quixote]

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@saic-trsc.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 mid-east 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.
>

[Arthur Wood]

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 built-in 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@saic-trsc.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 mid-east 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.
>>
>

[quixote]

Thank you!





"Arthur Wood" <wooda@saic-trsc.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 built-in 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@saic-trsc.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 mid-east 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.
>>>
>>
>

[Rick Rothstein]

> 2) for the number of weekdays in the interval, use the built-in 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

[Arthur Wood]

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 built-in 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
>
>

[Rick Rothstein]

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@saic-trsc.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 built-in 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
> >
> >
>

[Arthur Wood]

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@saic-trsc.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 built-in
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
>> >
>> >
>>
>
>

[Rick Rothstein]

Don't feel bad, the usage is not an obvious one. Also, when I first
attempted to use this functionality, I too mis-interpreted that option.

Rick

"Arthur Wood" <wooda@saic-trsc.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@saic-trsc.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 built-in
> 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
> >> >
> >> >
> >>
> >
> >
>