How do I count the number of business days between two dates

[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

 

[Douglas J. Steele]

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

 

[James A. Fortune]

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
(e-mail address removed)

 

[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

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

(no private e-mails, please)

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

 

[Arvin Meyer [MVP]]

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

 

[Douglas J Steele]

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

(no e-mails, please!)

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

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

(no private e-mails, please)

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