Calculate business days?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am putting together a Days To Ship report and want to the Lag time data on
my report. All I'm looking for is, an order comes in 11/02 (Fri) and Ships
11/05 (Mon) the output should be 1.

Any thoughts?

Thanks!
 
Create a table name CountNumber with number field named CountNUM and numbers
0 (zer0) through whatever your maximum lag time might be. Create a Holidays
table with your holidays in field named Holiday.
Use this query, changing table name and fields to match yours ----
SELECT William.[Pt#], DateAdd("d",[CountNUM],[Start dt]) AS [Lag Time]
FROM William, CountNumber, Holidays
WHERE (((Format(DateAdd("d",[CountNUM],[Start dt]),"w"))<>1 And
(Format(DateAdd("d",[CountNUM],[Start dt]),"w"))<>7) AND
((DateAdd("d",[CountNUM],[Start dt]))<>[Holiday]) AND
((DateAdd("d",[CountNUM],[Start dt])) Between [Start dt] And [End dt]))
GROUP BY William.[Pt#], DateAdd("d",[CountNUM],[Start dt])
ORDER BY William.[Pt#], DateAdd("d",[CountNUM],[Start dt]);
 
Oh yeah, build a query using the first one to count days like this --
SELECT [Dates DELIVERY].[Pt#], Count([Dates DELIVERY].[Lag Time]) AS
[CountOfLag Time]
FROM [Dates DELIVERY]
GROUP BY [Dates DELIVERY].[Pt#];
 
I am also in need of this ability. Were you ever able to find a solution and
if so what method did you use?

Thank You,

Guy
 
Back
Top