calculate working days between 2 dates

[KAquestions]

hi all,

i'd like to calculate the number of working days between [DateStart] and
[DateEnd].

can anyone please help?

k

 

[Golfinray]

In a separate field of the query:
My days:datediff("d",[datestart],[dateend])

 

[Michel Walsh]

The easier solution is to have a table of non-working days (which include
local holidays and weekend days) and to get the difference in days between
the two days from which you subtract the number of records in the
non-working days table:

( [ending date] - [starting date] + 1) - DCount('*", "NonWorkingDays",
"day BETWEEN #" & [starting date] & "# AND #" & [ending date] & "#")


where I assumed the table NonWorkingDays has one field, day. I also assume
your regional setting is US like for dates (ie. mm/dd/yyyy ).



Sure, your NonWorkingDays has to cover the range of dates which are of
relevant interest.



Vanderghast, Access MVP

 

[KAquestions]

Does that not just return the number of days?
I really need to know the number of working days, which would exclude the
weekends.

Kirstie

In a separate field of the query:
My days:datediff("d",[datestart],[dateend])

hi all,

i'd like to calculate the number of working days between [DateStart] and
[DateEnd].

can anyone please help?

k

 

[Al Campagna]

KAquestions,
On my website (below) I have a 97 and 2003 sample file (in Access Tips)
that demonstrates how to calculate the workdays between two dates.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Al Campagna]

KAquestions,
The samples are under the description "Calculate Number of Workdays
between 2 dates."
Yes... it does...
The Holiday table in the sample files contains 2008 holidays, so you can
test hop it using 2008 dates.
If and when you want to use it for real... you'll have to enter your
2009 holiday schedule.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Does that not just return the number of days?
I really need to know the number of working days, which would exclude the
weekends.

Kirstie

In a separate field of the query:
My days:datediff("d",[datestart],[dateend])

hi all,

i'd like to calculate the number of working days between [DateStart] and
[DateEnd].

can anyone please help?

k

 

[Al Campagna]

KAquestions,
Apologies KA, I thought your question about Golfinray's response was
directed at me.
**Please see my 1/13/09 9:31am response first.
As mentioned, you'll need to test my sample files using 2008 dates,
or... update the Holiday table to 2009, and test with 2009 date ranges.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Does that not just return the number of days?
I really need to know the number of working days, which would exclude the
weekends.

Kirstie

In a separate field of the query:
My days:datediff("d",[datestart],[dateend])

hi all,

i'd like to calculate the number of working days between [DateStart] and
[DateEnd].

can anyone please help?

k