Exluding Weekends from Query Calculation

[Mile-O]

In a query, is there anyway to calculate the difference between two dates, excluding all Saturdays and Sundays?

 

[ColinEssex]

Hi

Here's a zipped date file that may help you. Its got some useful stuff in it. You may be able to use the code in your query.

Col

 

[Autoeng]

Here is a link to a previous post of mine that pdx_man and Raskew helped me out with. I also have another query that excludes weekends and holidays from a table if you need it.

Autoeng

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=37220

 

[Mile-O]

Cheers, I'll have a look at both.

 

[Mile-O]

Still having no luck here:

Basically, in a table I have two fields called [Date Received] and [Date Resolved].

I want a third calculated field in the query called [Days Open] which is basically ([Date Resolved] - [Date Received]) + 1

The problem is that I need to exclude all Saturdays and Sundays from this calculation.

 

[Mile-O]

I got this solved now: here's the expression if anyone else is interested.

Days Open: ((DateDiff("d",[Date Received],Date(),2))-(Int(DateDiff("d",[Date Received],Date(),2)/7))*2)+IIf(Weekday(Date(),2)>5,5-Weekday(Date(),2),0)+IIf(Weekday([Date Received],2)=6,1,0)-IIf(Weekday([Date Received],2)>Weekday(Date(),2),2,0)