How do I calculate net working days in access 2003?

[danutz99]

I am trying to calculate the net working days for a report in Access 2003.
The datediff function does not appear to allow this specification and the
networkdays function from Excel is not available. Any assistance is greatly
appreciated.

 

[KARL DEWEY]

Use these two queries unless you know subqueries --
[Dates-Weekdays] ---
SELECT Joel_Allen.Sale, Joel_Allen.[Start Date], Joel_Allen.[End Date],
DateAdd("d",[CountNUM],[Start Date]) AS [Days Between]
FROM CountNumber, Joel_Allen
WHERE (((DateAdd("d",[CountNUM],[Start Date]))<=[End Date]))
ORDER BY Joel_Allen.Sale, DateAdd("d",[CountNUM],[Start Date]);

SELECT [Dates-Weekdays].Sale, [Dates-Weekdays].[Start Date],
[Dates-Weekdays].[End Date], Count([Dates-Weekdays].[Days Between]) AS
[CountOfDays Between]
FROM [Dates-Weekdays]
WHERE (((Weekday([Days Between])) Between 2 And 6))
GROUP BY [Dates-Weekdays].Sale, [Dates-Weekdays].[Start Date],
[Dates-Weekdays].[End Date];

 

[BugranT]

Hello,

The following will give you the elapsed business days between two dates. In
this case between Ordered and Received. Also included is a method for
eliminating company holidays (you would need to create a holiday table). I
hope this might help as I use this within a textbox on a report.

(([RcvdDate]-[OrderDate])-(DateDiff("ww",[ OrderDate],[
RcvdDate])*2))-DCount("[HolidayDt]","dbo_tbl_holidays","[ HolidayDt] > #" &
[OrderDate] & "# and [HolidayDt] < #" & [RcvdDate] & "#")

Essentially, this takes the total days between the two dates and subtracts
the number of weekends (*2 for two days) and then also subtracts the number
of company holidays the expression found in the dbo_tbl_holidays table that
fell between the two dates.

Bug