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
    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
    recount Blogs Oct 17, 2015

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

    > >
    > >
    > >
     

Share This Page