Access Query - dteDate1 - dteDate2 = #business days 2

[kwkokorelis]

Greetings;

I am trying to write a query that subtracts one date from another and gives me the difference in business days (excluding Sat and Sun.)

Could anyone help?

Thanks.

 

[JerryDennison]

Use the DateDiff() function. It can return the number of weekdays between the two dates for you automatically. You can even tell it what day of the week your week starts with. You'll find syntax and usage under help.

 

[MichaelRed]

Sorry Jerry,

The datediff function does not just count the weekdays, but all of the days between the dates.

Some time ago, I posted a FAQ on the WeekDays on behalf of another user.

kwkokorelis,

In the FAQ, I have included code to use a table of holiday dates. Each time I have run across this question, it ends up needing the holidays as well as the weekend dates to be excluded from the calculation.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[JerryDennison]

I beg to differ. The DateDiff function will return only workdays between two dates and you may specify which day of the week your workdays begin. It will not return holidays, this is true, but it will return only workdays.

 

[MichaelRed]

Jerry,

Please privide an example of your returning the difference in two dates in WORKDAYS.


I get the following results:
? datediff("d", #1/1/2001#, #2/26/2001#), datediff("w", #1/1/2001#, #2/26/2001#)
56 8
'_____________________________________________________
The first is 56 CALENDAR days from the first of the year, while the second is eight weeks. My Help does not show any option to only count WEEKDAYS/WORKDAYS.

I would realy appreciate knowing an easier way to do this than what I have concocted.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[JerryDennison]

May the fleas of 1000 camels infest my armpits! I stand corrected. DateDiff("w&quot returns the weeks between the two dates based on the number of workdays between(excluding the first date). Please remove any stars given me for my answers are erroneous.

 

[MichaelRed]

? based on the number of workdays between ...

Shouldn't it be:

based on the number of weekdays between ...
MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[JerryDennison]

Yes, it should've read weekdays. Unfortunately there is no room for any more fleas. ;-)