Calculate between two dates

  • Thread starter Thread starter Cam
  • Start date Start date
C

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
 
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
Click to expand...

DateDiff("d", startdate, enddate) - DCount("*", "qryHolidays", "[holidaydate]
= #" & startdate & "# AND [holidaydate] <= #" & enddate & "#")
Click to expand...

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).
 
Thanks John,

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

John W. Vinson said:
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
Click to expand...

DateDiff("d", startdate, enddate) - DCount("*", "qryHolidays", "[holidaydate]
= #" & startdate & "# AND [holidaydate] <= #" & enddate & "#")
Click to expand...

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).
Click to expand...
 
Thanks John,

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

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.
 
Back
Top