DarrelS said:
I am trying to return a scheduled date based on duration and either a start
or a finish date, but selecting only working days excluding weekends and
holidays. Thanks.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Here is a function I picked up somewhere that gets workdays (defined as
Mon-Fri):
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
Since holidays are different the world 'round the usual solution is to
have a table of holidays:
holiday_date holiday_name
1jan2005 New Year
14jun2005 Bastille Day
.... etc. ...
Then check if any of the holiday_dates are in the date range you are
considering:
PARAMETERS [Start Date] Date, [End Date] Date;
SELECT Count(*)
FROM Holidays
WHERE holiday_date BETWEEN [Start Date] And [End Date]
Subtract the result from the Work_Days returned by the function.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQhpLmIechKqOuFEgEQIinACg21INodWEXuNS8Zo1umez1+XAeNAAoPnV
M0iClOrjaQRP8F8McCw68OFb
=yyMm
-----END PGP SIGNATURE-----
