Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

How Do I Count The Number Of Business Days Between Two Dates

Discussion in 'Information Technology' started by Mazzara, Jul 28, 2006.

  1. Mazzara

    Mazzara
    Expand Collapse
    Guest

    I know that there are a number of formulas to do this calculation, but I am
    getting quite confused.

    I need to perform this calculation in a query:

    Currently my formula is DateDiff("d",[DateReceived],[DateClosed]) and I get
    the right result for the number of calendar days but I need to get the number
    of working days excluding weekends.

    Thanks
     
  2. Loading...

    Similar Threads Forum Date
    Why Count Numbers? Sikh Sikhi Sikhism Feb 7, 2008
    What Counts As "sangat" To You? Interfaith Dialogues Mar 15, 2017
    Ashdoc's Movie Review---The Accountant Theatre, Movies & Cinema Oct 21, 2016
    Gurmat Vichaar | Futility Of Counting Paaths | April 12, 2016 Gurmat Vichaar Apr 23, 2016
    Wolf Count Blogs Oct 17, 2015

  3. OP
    Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    Take a look in the Date/Time section of "The Access Web"
    http://www.mvps.org/access/, or at my September, 2004 "Access Answers"
    column in Pinnacle Publication's "Smart Access".

    You can download the column (and sample database) for free at
    http://www.accessmvp.com/djsteele/SmartAccess.html

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "Mazzara" <Mazzara@discussions.microsoft.com> wrote in message
    news:40417F2C-D067-43AA-BF96-6689A015C5AC@microsoft.com...
    >I know that there are a number of formulas to do this calculation, but I am
    > getting quite confused.
    >
    > I need to perform this calculation in a query:
    >
    > Currently my formula is DateDiff("d",[DateReceived],[DateClosed]) and I
    > get
    > the right result for the number of calendar days but I need to get the
    > number
    > of working days excluding weekends.
    >
    > Thanks
     
  4. OP
    James A. Fortune

    James A. Fortune
    Expand Collapse
    Guest

    Mazzara wrote:
    > I know that there are a number of formulas to do this calculation, but I am
    > getting quite confused.
    >
    > I need to perform this calculation in a query:
    >
    > Currently my formula is DateDiff("d",[DateReceived],[DateClosed]) and I get
    > the right result for the number of calendar days but I need to get the number
    > of working days excluding weekends.
    >
    > Thanks


    It is my guess that Douglas' code will do what you want. I offer the
    following as an alternative:

    http://groups.google.com/group/comp.databases.ms-access/msg/548d33651b087c05

    James A. Fortune
    MPAPoster@FortuneJames.com
     
  5. OP
    Mazzara

    Mazzara
    Expand Collapse
    Guest

    Hi Douglas

    I have had a look and copied your module across but I am still getting
    errors. If you could provide further assistance that would be fantastic.

    Currently I have the two date fields DateReceived and DateClosed. I have
    the following in the field of query design.

    ReceivedDays: (DateDiff("d",[datereceived],[dateclosed]))

    How do I now apply the calculation of business days?



    In my querie I have

    "Douglas J. Steele" wrote:

    > Take a look in the Date/Time section of "The Access Web"
    > http://www.mvps.org/access/, or at my September, 2004 "Access Answers"
    > column in Pinnacle Publication's "Smart Access".
    >
    > You can download the column (and sample database) for free at
    > http://www.accessmvp.com/djsteele/SmartAccess.html
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no private e-mails, please)
    >
    >
    > "Mazzara" <Mazzara@discussions.microsoft.com> wrote in message
    > news:40417F2C-D067-43AA-BF96-6689A015C5AC@microsoft.com...
    > >I know that there are a number of formulas to do this calculation, but I am
    > > getting quite confused.
    > >
    > > I need to perform this calculation in a query:
    > >
    > > Currently my formula is DateDiff("d",[DateReceived],[DateClosed]) and I
    > > get
    > > the right result for the number of calendar days but I need to get the
    > > number
    > > of working days excluding weekends.
    > >
    > > Thanks

    >
    >
    >
     
  6. OP
    Arvin Meyer [MVP]

    Arvin Meyer [MVP]
    Expand Collapse
    Guest

    Try some of the functions in the Date/Time section at the Access Web:

    http://www.mvps.org/access/datetime/index.html

    one that you should just copy to a module is at:

    http://www.mvps.org/access/datetime/date0012.htm

    Also see the following at my website:

    http://www.datastrat.com/Code/GetBusinessDay.txt
    --
    Arvin Meyer, MCP, MVP
    Microsoft Access
    Free Access downloads
    http://www.datastrat.com
    http://www.mvps.org/access

    "Mazzara" <Mazzara@discussions.microsoft.com> wrote in message
    news:40417F2C-D067-43AA-BF96-6689A015C5AC@microsoft.com...
    >I know that there are a number of formulas to do this calculation, but I am
    > getting quite confused.
    >
    > I need to perform this calculation in a query:
    >
    > Currently my formula is DateDiff("d",[DateReceived],[DateClosed]) and I
    > get
    > the right result for the number of calendar days but I need to get the
    > number
    > of working days excluding weekends.
    >
    > Thanks
     
  7. OP
    Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    If you've copied the module into your application, you can use

    ReceivedDays: WorkDayDiff([datereceived],[dateclosed])

    If you haven't copied the module, you can use:

    ReceivedDays: DateDiff("d", [datereceived], [dateclosed]) - DateDiff("ww",
    [datereceived], [dateclosed], 1) * 2 - IIf(Weekday([dateclosed], 1) = 7,
    IIf(Weekday([datereceived], 1) = 7, 0, 1), IIf(Weekday([datereceived], 1) =
    7, -1, 0))

    (where's that supposed to be all one line!)

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Mazzara" <Mazzara@discussions.microsoft.com> wrote in message
    news:65809929-D1EF-4B20-8AB3-4B6AA48290CD@microsoft.com...
    > Hi Douglas
    >
    > I have had a look and copied your module across but I am still getting
    > errors. If you could provide further assistance that would be fantastic.
    >
    > Currently I have the two date fields DateReceived and DateClosed. I have
    > the following in the field of query design.
    >
    > ReceivedDays: (DateDiff("d",[datereceived],[dateclosed]))
    >
    > How do I now apply the calculation of business days?
    >
    >
    >
    > In my querie I have
    >
    > "Douglas J. Steele" wrote:
    >
    > > Take a look in the Date/Time section of "The Access Web"
    > > http://www.mvps.org/access/, or at my September, 2004 "Access Answers"
    > > column in Pinnacle Publication's "Smart Access".
    > >
    > > You can download the column (and sample database) for free at
    > > http://www.accessmvp.com/djsteele/SmartAccess.html
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no private e-mails, please)
    > >
    > >
    > > "Mazzara" <Mazzara@discussions.microsoft.com> wrote in message
    > > news:40417F2C-D067-43AA-BF96-6689A015C5AC@microsoft.com...
    > > >I know that there are a number of formulas to do this calculation, but

    I am
    > > > getting quite confused.
    > > >
    > > > I need to perform this calculation in a query:
    > > >
    > > > Currently my formula is DateDiff("d",[DateReceived],[DateClosed]) and

    I
    > > > get
    > > > the right result for the number of calendar days but I need to get the
    > > > number
    > > > of working days excluding weekends.
    > > >
    > > > Thanks

    > >
    > >
    > >
     
Since you're here... we have a small favor to ask...     Become a Supporter      ::     Make a Contribution     


Share This Page