How do I calculate net working days in access 2003?

  • Thread starter Thread starter danutz99
  • Start date Start date
D

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