Determining Weekdays in a Date Range

[Lori Jo]

I have an orders table that I want to determine the cycle
time in workdays between 4 fields.

tbl_Orders
CreatedDate CreatedTime CompleteDate CompleteTime
6/4/2004 12:53:04 6/8/2004 17:01:05
6/3/2004 08:04:58 6/9/2004 12:00:41
6/2/2004 10:05:47 6/4/2004 08:41:04

I have figured out how to combine the Date/Time fields
into 2 fields for analysis:

CreatedDateTime CompleteTime
6/4/2004 12:53:04 6/8/2004 17:01:05
6/3/2004 08:04:58 6/9/2004 12:00:41
6/2/2004 10:05:47 6/4/2004 08:41:04

I can do a calculation to determine the difference betwen
the 2. Beyond that I am clueless. I want to be able to
do this in Access, so all my stuff resides in 1 place. I
believe that Excel can do this with a formula.

So my question is: How do I determine the cycle time
between these 2 fields for the same record in WEEKDAYS.
Furthermore, can I determine the cycle time in WEEKDAYS
and WORK HOURS?

I am very stumped and this problem is very important to a
project I have been asked to submit to a customer. Any
assistance or direction would be greatly appreciated.

-- Lori Jo

 

[Michel Walsh]

Hi,


Date_time should optimally be stored in one field.

DateDiff("d", lateDateTime, recentDateTime)


return the number of time the date change between the two dates, but that
would include the weekend days. I suggest you use one of the functions
proposed in http://www.mvps.org/access/datetime/date0012.htm to get the
working-days, such as dhCountWorkdaysA( ). In a query, that can be just
like:


SELECT dhCountWOrkdaysA(StartingDate, EndingDate)
FROM myTable


after you copy and paste the programs in a standard (new) module of your
application.



Hoping it may help,
Vanderghast, Access MVP