Welcome to SPN

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

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

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

    > >
    > >
    > >
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page