Turnaround Time

  • Thread starter Thread starter Tony
  • Start date Start date
T

Tony

At work, we receive documents, look them over, and ship
them out to the original sender. Thus, there is an
arrival date and a shipped-out-date.

My dilemma is calculating the number of business days
(Monday through Friday) it takes for us to process the
submittal.

Is there some function in Microsoft Access 2000 that
automatically calculates the number of business days
between two dates? -- the two dates are entered in data
fields, so, again, I'm looking for a way to automatically
display the business days between these two dates.

Any help would be greatly appreciated... I've been trying
to figure this out for several days now. Thanks.
 
But what is a business day? In order for ACCESS to identify such a day(s),
you must be able to define it. Is it all Monday through Friday dates? Is it
all Monday through Friday dates except when the date is 31? Is it just
Fridays? When is a weekday not a business day (e.g., when it's a holiday?
and if yes, then what are the list of holidays)?

Essentially, unless you want to assume that all Mondays through Fridays are
business days, then you'll need to set up a table that contains all days
that are not business days and then do comparisons against that table's data
as you loop through all the days between the start and end dates. Not
extremely complicated, but not trivial either. Will require use of VBA code
to do this. I am not aware of a generic function that will do this for you.
 
Hi Tony

To calculate the business days between 2 dates, you can use the following
function. However, this does not account for days where the business might be
closed.

Function Work_Days (BegDate As Variant, EndDate As Variant) As Integer
' Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

BegDate = DateValue(BegDate)

EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt < EndDate
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
End Function


Hope this helps

Maurice St-Cyr
Micro Systems Consultants, Inc.
 
If you can get by using Excel there's a formula
called "NETWORKDAYS" that will calculate the amount of
work days between two dates.

Joe
 
Back
Top