Counting dates between 2 dates (No weekends)

  • Thread starter Thread starter Sandra
  • Start date Start date
S

Sandra

I need a query (and where to place it) the counting the numbers of days
between two dates (not counting weekends)
 
If the two dates are without time (ie, time is 00:00:00), then

1+ date2 - date1

give the number of days between the two dates (you may remove the +1 if you
want exclusive rather than inclusive).


You could also used:

1 + DateDiff("d", earliestDate, latestDate).


Next, to get the number of Sunday, between two dates, none of the date being
itself a Sunday, you can use:

DateDiff("ww", earliestDate, latestDate, vbSunday)


and to get the number of Saturday:


DateDiff("ww", earliestDate, latestDate, vbSaturday)



So, in a query, in the query designer, in the grid, add the computed
expression:



1 + DateDiff("d", earliestDate, latestDate) -DateDiff("ww",
earliestDate, latestDate, 1)-DateDiff("ww", earliestDate, latestDate, 7)



That works only if none of the date is a Saturday, neither a Sunday.




Hoping it may help,
Vanderghast, Access MVP
 
When I do this I am getting a compile error?

This is what I am using

Total days: 1+DateDiff("d",[Date Issued],[Signature
Date])-DateDiff("ww",[Date Issued],[Signature Date],1)-DateDiff("ww",[Date
Issued],[lSignature Date],7)
 
There is a [lSignature Date], should be [Signature Date].


Check the References... (In the VBE, under the menu Tools) Is there a
checked reference which starts its description with the word "MISSING" ? If
so, uncheck it, or fix it.


Hoping it may help,
Vanderghast, Access MVP


Sandra said:
When I do this I am getting a compile error?

This is what I am using

Total days: 1+DateDiff("d",[Date Issued],[Signature
Date])-DateDiff("ww",[Date Issued],[Signature Date],1)-DateDiff("ww",[Date
Issued],[lSignature Date],7)
--
Sandra


Sandra said:
I need a query (and where to place it) the counting the numbers of days
between two dates (not counting weekends)
Click to expand...
Click to expand...
 
Back
Top