Calculate between two dates

[Cam]

Hello,

I got a query trying to calculate the number of days between two days
excluding weekend and holiday. I have a query for qryHolidays. How to put in
formula in query to achieve this? Thanks

 

[John W. Vinson]

Hello,

I got a query trying to calculate the number of days between two days
excluding weekend and holiday. I have a query for qryHolidays. How to put in
formula in query to achieve this? Thanks

DateDiff("d", startdate, enddate) - DCount("*", "qryHolidays", "[holidaydate]

= #" & startdate & "# AND [holidaydate] <= #" & enddate & "#")

Use datediff to calculate all days, and then count the number of holidays in
the same range and subtract. (I'll assume weekends are in your holidays
table).

 

[Cam]

Thanks John,

Weekend is not part of the Holiday table. What do I need to modify the
formula to exclude the weekend also?

Hello,

I got a query trying to calculate the number of days between two days
excluding weekend and holiday. I have a query for qryHolidays. How to put in
formula in query to achieve this? Thanks

DateDiff("d", startdate, enddate) - DCount("*", "qryHolidays", "[holidaydate]

= #" & startdate & "# AND [holidaydate] <= #" & enddate & "#")

Use datediff to calculate all days, and then count the number of holidays in
the same range and subtract. (I'll assume weekends are in your holidays
table).

 

[John W. Vinson]

Thanks John,

Weekend is not part of the Holiday table. What do I need to modify the
formula to exclude the weekend also?

You can probably use the code from:

http://www.mvps.org/access/datetime/date0006.htm

There are other techniques; it would be a pretty easy fix to just add
Saturdays and Sundays to your table as well. You can create such a table using
Fill... Series in Excel and copy the results into your table.