Calculate Business Days Only

[Ray]

Hi:

i'm attempting to setup a query where i can caluculate the
difference between a start date and an end date (using
DateDiff("d",[OpenDate],[CloseDate]))...

Is there a way to also factor in business days only have
that value returned?

For example: i started on 9/10/04 and ended on 9/13/04.
Instead of showing a return value of 4 days (10th, 11th,
12th, 13th), i want it to return a value of 2 days (10th,
13th)....

Any assistance would be appreciated...

Thanks,
Ray

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I can't remember if I found this function or created it myself. Anyway,
it seems to work.

Holidays are a function of each organization. The usual method used to
ignore holidays in a work_day count is to keep a table of holiday dates.
Then count the number of holidays between the beginning date and the
ending date; then subtract that number from the number returned by the
function Work_Days.

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 Day(DateCnt) Mod 6 <> 1 Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
End Function

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQUhiooechKqOuFEgEQIFAACgmTpB23PU7EeQ2kQ353EuAv/kHpMAnA0b
iDMDblENLmpd4KIG30KKgjK4
=QRu7
-----END PGP SIGNATURE-----