Weekdays & Holidays

[Rseven]

Hello,

I'm trying to write a module that excludes weekdays and holidays from a date calculation....this is what I have for the weekdays and was wondering if something could be added to it for the holidays....

Function workdays(Begdate As Variant, enddate As Variant) As Integer

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
workdays = WholeWeeks * 5 + EndDays
End Function

 

[CautionMP]

Here are two ways to do it:[ol]
[li]Using a table to store holidays:
[navy]Microsoft: Access Modules (VBA Coding) > FAQs: [/navy]faq181-261 [navy]Calculating a future Workday Date[/navy][/li]
[li]Calculating the differnce on the fly using standard holidays:
[navy]Microsoft: Access Modules (VBA Coding) Forum: [/navy]
Thread705-585432 [navy]Finding the difference between two dates[/navy][/li][/ol]

Hope this helps:
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[mp9]

If it helps at all, the function at

http://www22.brinkster.com/accessory/modules/003.shtml

is an easy (and very fast) method for determining the number of weekdays between two dates. Doesn't account for holidays though.