Recognize weekend

[Teuntja123]

Hello all,

In my query I made an extra column as follows:
Leadtime: [Received dt] - [Vrijgave dt]
Both are dates.
In the section criteria, I only want the records which are equal to 1 (difference in days must be 1 day).

The problem is that I need to count with workdays. In current situation, when the Received dt = friday and the Vrijgave dt = monday it shows an 3, but i want to let it show a 1 because the difference in workdays is only 1.

Can someone help me?

Teun.

 

[namliam]

Skipping the weekends is relatively easy, however what about national holidays like easter, xmass, new year's, etc...

For finding all holidays between two dates, I found this after a quick search on this site:
http://www.access-programmers.co.uk/forums/showthread.php?t=179005&highlight=Workday

 

[Teuntja123]

Can you tell me how to skip the weekends relatively easy?
The holidays is nice, but not so important for me.
I hope it can be changed in just the query criteria because Iam not familiar with VBA.

Thanks in advance

 

[namliam]

Assuming your start and end dates are always workdays:
[Endate]-[StartDate]-(Int(([Enddate]-[StartDate])/7)+IIf(Weekday([EndDate],2)<Weekday([StartDate],2),1,0))*2

How it works:
[Endate]-[StartDate] >> Calculate the difference in calander days
a) Int(([Enddate]-[StartDate])/7) >> For every full week count 1
b) IIf(Weekday([EndDate],2)<Weekday([StartDate],2),1,0) >> If the start date is later in the week than the enddate ... count 1
add (a + b ) * 2 (weekend days) and presto you should get this list:

Startdate    	EndDate   	Workdays
10/4/2013   	10/07/2013	1
10/4/2013  	10/08/2013	2
10/4/2013  	10/09/2013	3
10/4/2013  	10/10/2013	4
10/4/2013  	10/11/2013	5
10/4/2013  	10/14/2013	6
10/4/2013  	10/15/2013	7
10/4/2013  	10/16/2013	8
10/4/2013  	10/17/2013	9
10/4/2013  	10/18/2013	10
10/4/2013  	10/21/2013	11
10/4/2013  	10/22/2013	12

 

[Teuntja123]

Thank you Namliam!!
I changed the , in ; and could paste it directly

Many thanks!