DateDiff ("D") Not calculating weekdays only

[Hfnet]

I am using a GetDate Module to calculate working days because DateDiff("D", xxx, xxx, 2) is calculating every day including weekends, howver this is slowing down the query time due to the amount of records processed.

How can I use DateDiff in a query to return weekdays only?

Ask not what you can do for your boss, ask how much it's worth.

 

[Hfnet]

Apologies, I meant to use DateDiff("W", xxx, xxx, 2), NOT "D"

It still does not return weekdays even though ms Access Help indicates that "W" whould return weekdays

Ask not what you can do for your boss, ask how much it's worth.

 

[PHV]

Have a look here:

http://www.tek-tips.com/faqs.cfm?fid=261

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Hfnet]

Yes, I have been using a similar module to get my dates, but it slows the query down dramatically when I am processing thousands of records.

What I wanted was a native way of calculating weekdays (holiday days not important)

Ask not what you can do for your boss, ask how much it's worth.

 

[RoyVidar]

Reading this Microsoft article ACC: "W" Option of the DateDiff() Function Does Not Work, shed some light on why "W" doesn't work as one might expect. There's also a workaround function in the link, which doesn't exclude extra holidays, so it might be more efficient than the one you're using (though I haven't tried).

I think that's the options you have
* tweak/optimize your custom function
* tweak/optimize your query

- there might of course be other alternatives too ...

Roy-Vidar

 

[mp9]

I use the code from

http://www22.brinkster.com/accessory/modules/003.shtml

to calculate the number of weekdays between two dates, and I find it to be rather fast.

Hope this helps.