Difference between 2 dates

[David Gartrell]

Hi Everyone,

I wonder if someone can help me please. I have an SQL query in Access 2000
that works out the number of days between 2 given dates, for example the
difference between Feb 15th & Feb 19th is 4 days. However this includes the
Saturday & sunday. What i'd really like to achieve is to have the query
return just the number of weekdays elapsed rather than days in general. If
it's of any help both the start date and end date will always be a week day.

Thanks very much in advance

David.

 

[Ron2006]

If you ignore holidays................


Subject: Computing working days between two dates.



Compute the number of working days between two dates:

WorkDays=DateDiff("d",[StartDate],[EndDate])-(DateDiff("ww",
[StartDate],[EndDate
],7)+DateDiff("ww",[StartDate],[EndDate],1))


(watch out for wrapping created by the posting program)

First datediff is total number of days between the dates.
Second datediff is number of Saturdays between the dates
Third datediff is number of Sundays between the dates.

Workdays is therefore equal to total number of days less the number of
Saturdays and less the number of Sundays.

Ron

 

[John W. Vinson]

Hi Everyone,

I wonder if someone can help me please. I have an SQL query in Access 2000
that works out the number of days between 2 given dates, for example the
difference between Feb 15th & Feb 19th is 4 days. However this includes the
Saturday & sunday. What i'd really like to achieve is to have the query
return just the number of weekdays elapsed rather than days in general. If
it's of any help both the start date and end date will always be a week day.

Thanks very much in advance

David.

I presume you will also want to exclude company holidays? If so you'll need a
holidays table as well. In any case the simplest solution is to use some VBA
code. There are many examples but here's a commonly used one:

http://www.mvps.org/access/datetime/date0012.htm