|
Subject:
|
Calculate Workdays minus holidays
|
|
Posted By:
|
woodan
|
Post Date:
|
2/11/2004 12:26:02 PM
|
I have the following code in a module:
Private Sub END_DATE_AfterUpdate()
Dim dif As Integer
Dim i As Integer
Dim dCount As Integer
Dim midTemp
dCount = 1
dif = DateDiff("d", START_DATE, END_DATE, vbMonday, vbFirstJan1)
For i = 1 To dif
midTemp = DateAdd("d", i, START_DATE)
If Weekday(midTemp, vbMonday) < 6 Then
dCount = dCount + 1
End If
Next i
NUM_DAYS = dCount
End Sub
I used the code from an existing post(changed accordingly) and it works great. It pulls the Start and End date from my form. Now I would like to be able to have the code subtract out holidays that I determine. Any easy ideas?
Thanks.
Dan
|
|
Reply By:
|
SerranoG
|
Reply Date:
|
2/11/2004 3:17:55 PM
|
Dan,
Clever code...
FYI: the DateAdd and DateDiff functions already have a "w" option (weekday) that counts weekdays already, so no need to use "d" (day) and then pull out weekends manually.
As for holidays, since they vary from worksite to worksite, the easiest thing I can think of is for you to create a holidays table with fields strName, intMonth, and intDay. Omit the year because you want your table to work for reoccuring holidays.
In your code, loop through the table, construct a current holiday
dtmHoliday = DateSerial(Year(Date()), intMonth, intDay)
if it falls between the start and end dates, then subtract it from your total.
Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
|
