Holiday en Weekend dagen.

[klablabla]

Van de volgende website de database gehaald en de nieuwe feestdagen erin gezet.
http://msdn.microsoft.com/en-us/library/dd327646(v=office.12).aspx
Helaas als ik de lijst erin zet werkt hij niet zoals het zou werken.
Desctiption Holiday
Nieuwjaarsdag 1/1/2012
bankholiday 1 2/1/2012
bankholiday 2 6/4/2012
1e paasdag 8/4/2012
2e paasdag 9/4/2012
koningendag 30/4/2012
Hemelvaartsdag 17/5/2012
1e pinksterdag 27/5/2012
2e pinksterdag 28/5/2012
bankholiday 3 29/5/2012
1e kerstdag 25/12/2012
2e kerstdag 26/12/2012
nieuwjaarsdag 1/1/2013

Als je 2/4/2012 t/m 10/4/2012 invoer krijg je -1

Haal ik de feestdagen eruit die in het weekend vallen krijg ik 1.
terwijl het 4 hoort te zijn.

 

[klablabla]

Het werkt

Na wat zoeken werkt het volgende perfect.

Function funWorkDaysDifference(dtStartDay As Date, dtEndDay As Date) As Long

Dim lngTotalDays As Long
Dim lngTotalWeeks As Long
Dim dtNominalEndDay As Date
Dim lngTotalHolidays As Long
Dim lngstart As Long
Dim lngend As Long


'Check to see if dtStartDay > dtEndDay.  If so, then switch the dates
If dtStartDay > dtEndDay Then
    dtNominalEndDay = dtStartDay
    dtStartDay = dtEndDay
    dtEndDay = dtNominalEndDay
End If
'Here are how many weeks are between the two dates
lngTotalWeeks = DateDiff("w", dtStartDay, dtEndDay)
'Here are the number of weekdays in that total week
lngTotalDays = lngTotalWeeks * 5
'Here is the date that is at the end of that many weeks
dtNominalEndDay = DateAdd("d", (lngTotalWeeks * 7), dtStartDay)
'Now add the number of weekdays between the nominal end day and the actual end day
While dtNominalEndDay <= dtEndDay
    If WeekDay(dtNominalEndDay, 2) <> 6 Then
        If WeekDay(dtNominalEndDay, 2) <> 7 Then
            lngTotalDays = lngTotalDays + 1
        End If
    End If
    dtNominalEndDay = dtNominalEndDay + 1
Wend

'convert end date and startdate into long integer format for the DCount operation to avoid misreading of dates as US format
lngstart = dtStartDay
lngend = dtEndDay

'Here are how many holiday days there are between the two days
lngTotalHolidays = DCount("dtObservedDate", "tblHolidays", "dtObservedDate <= " & lngend & " AND dtObservedDate >= " & lngstart & " AND Weekday(dtObservedDate,2) <> 6 AND Weekday(dtObservedDate,2) <> 7")

'lngTotalHolidays = DCount("dtObservedDate", "tblHolidays", "int(dtObservedDate) <= " & Int(dtEndDay) & " AND int(dtObservedDate) >= " & Int(dtStartDay) & " AND Weekday(dtObservedDate,2) <> 6 AND Weekday(dtObservedDate,2) <> 7")

'Here are how many total days are between the two dates - this is inclusive of the start and end date
funWorkDaysDifference = lngTotalDays - lngTotalHolidays

End Function