calculate working days between 2 dates

  • Thread starter Thread starter KAquestions
  • Start date Start date
K

KAquestions

hi all,

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

can anyone please help?

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

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

KAquestions said:
hi all,

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

can anyone please help?

k
Click to expand...
Click to expand...
 
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."


KAquestions said:
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

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

KAquestions said:
hi all,

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

can anyone please help?

k
Click to expand...
Click to expand...
Click to expand...
 
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."

KAquestions said:
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

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

KAquestions said:
hi all,

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

can anyone please help?

k
Click to expand...
Click to expand...
Click to expand...
 
Back
Top