Holidays and Weekends


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 10 of 10

Thread: Holidays and Weekends

  1. #1
    quixote Guest

    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.

  2. #2
    Arthur Wood Guest

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



  3. #3
    quixote Guest

    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@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.

    >



  4. #4
    Arthur Wood Guest

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

    >>

    >



  5. #5
    quixote Guest

    Re: Holidays and Weekends


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

    >>

    >



  6. #6
    Rick Rothstein Guest

    Re: Holidays and Weekends

    > 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



  7. #7
    Arthur Wood Guest

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



  8. #8
    Rick Rothstein Guest

    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@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
    > >
    > >

    >




  9. #9
    Arthur Wood Guest

    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@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
    >> >
    >> >

    >>

    >
    >



  10. #10
    Rick Rothstein Guest

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

    > >
    > >

    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

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