Difference between 2 dates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David Gartrell - 20 Feb 2008 16:10 GMT

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.

Reply to this Message

Ron2006 - 20 Feb 2008 17:13 GMT

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

Reply to this Message

John W. Vinson - 20 Feb 2008 17:23 GMT

>Hi Everyone,
>
[quoted text clipped - 8 lines]
>
>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

Signature

John W. Vinson [MVP]

Reply to this Message