Exclude holiday from working days

[J_L]

Hi all,

I have tried to exclude weekends from the working days. Shown below is the code. My problem is how to exclude holidays also.. If the holiday falls on sunday, monday is also a public holiday.. http://vbaexpress.com/forum/images/smilies/banghead.gif
Thanks a lot..

Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

On Error GoTo Err_Work_Days

Set DB = CurrentDb()
Set rs = DB.OpenRecordset("Holiday")

BegDate = DateValue(fStartDate)
EndDate = DateValue(fEndDate)
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

Work_Days = WholeWeeks * 5 + EndDays - 1
Exit Function
Err_Work_Days:
If Err.Number = 94 Then
Work_Days = 0
Exit Function
Else
If Err.Number = 13 Then
Work_Days = 0
Exit Function
End If
End If

End Function

[XLGibbs]

There is a NetWorkDay function available..which has arguments fro date parameters and holidays....to which each argument can be multiple dates...

I believe it is part of the Analysis Toolpak Add In, which comes installed, you just have to check the box in Tools>Addins to see if it is checked off...

Whooops...my bad...thought this was an excel question...didn't see the whoe Recordset business.

[XLGibbs]

It seems like you could just do a count of days minues the records in that range that appear in the Holidays recordset...

You calculate your datediff between beg/end and get the workdays okay, so you just need to take that number and subtract the COUNT of holiday dates that fall between beg and end date..

sqlHolidays = "Select Count(*) From [Holidays] Where Date between #" & BegDate & "# and #" & endDate & "#"

Which would give you a count of the holiday dates between begin and end dates passed... subtract that number from the other calculation....

perhaps?

[matthewspatrick]

I use a UDF to calculate working hours between a start datetime and an end datetime, where I can define which weekdays are workdays and what the daily start and end working times are. You might want to try it out here:




Function WorkingHrs(StartAt As Date, EndAt As Date, WorkStart As Date, WorkEnd As Date, Workdays As String, _
ParamArray Holidays())
' Function calculates working hours available within a specified datetime range, allowing for
' scheduled working hours, non-working days, and holidays (if desired)

' Workdays specifies days employees normally work. For example, to use Mon - Fri, use 23456.
' To do just Tue & Thu, use 35; etc.

' assumes scheduled working hrs are the same on each working day!

Dim Counter As Long
Dim Dict As Object
Dim x As Variant
Dim Days(1 To 7) As Boolean
Dim WorkThisDay As Boolean
Dim HolThisDay As Boolean
Dim DateToday As Date
Dim DayStart As Date
Dim DayEnd As Date

' array indicates whether that weekday is a regular workday. Initialize to False
Days(1) = False
Days(2) = False
Days(3) = False
Days(4) = False
Days(5) = False
Days(6) = False
Days(7) = False

' populate array with results from Workdays argument
For Counter = 1 To Len(Workdays)
Days(Val(Mid(Workdays, Counter, 1))) = True
Next

On Error GoTo Cleanup

' populate holiday array
If Not IsMissing(Holidays) Then
Set Dict = CreateObject("Scripting.Dictionary")
For Each x In Holidays
If Not Dict.Exists(Format(x, "m/d/yyyy")) Then Dict.Add Format(x, "m/d/yyyy"), Format(x, "m/d/yyyy")
Next
End If

'loop through days in datetime range
For Counter = Int(StartAt) To Int(EndAt)
DateToday = CDate(Counter)
' determine if regular workday
WorkThisDay = Days(Weekday(DateToday, vbSunday))
' determine if holiday
If IsMissing(Holidays) Then
HolThisDay = False
Else
If Dict.Exists(Format(DateToday, "m/d/yyyy")) Then HolThisDay = True Else HolThisDay = False
End If
' if regular workday and not a holiday, figure out hrs from that day
If WorkThisDay And Not HolThisDay Then
' starts and ends on same day
If Int(StartAt) = Int(EndAt) Then
DayStart = IIf(CDate(StartAt - Int(StartAt)) > WorkStart, CDate(StartAt - Int(StartAt)), WorkStart)
DayEnd = IIf(CDate(EndAt - Int(EndAt)) < WorkEnd, CDate(EndAt - Int(EndAt)), WorkEnd)
WorkingHrs = IIf(CDbl(DayEnd - DayStart) < 0, 0, CDbl(DayEnd - DayStart))
' first day, if first day <> last day
ElseIf Counter = Int(StartAt) Then
DayStart = IIf(CDate(StartAt - Int(StartAt)) > WorkStart, CDate(StartAt - Int(StartAt)), WorkStart)
DayEnd = WorkEnd
WorkingHrs = IIf(CDbl(DayEnd - DayStart) < 0, 0, CDbl(DayEnd - DayStart))
' days in between start and end, if any
ElseIf Counter > Int(StartAt) And Counter < Int(EndAt) Then
WorkingHrs = WorkingHrs + CDbl(WorkEnd - WorkStart)
' last day, if first day <> last day
ElseIf Counter = Int(EndAt) Then
DayStart = IIf(CDate(EndAt - Int(EndAt)) > WorkStart, WorkStart, CDate(EndAt - Int(EndAt)))
DayEnd = IIf(CDate(EndAt - Int(EndAt)) < WorkEnd, CDate(EndAt - Int(EndAt)), WorkEnd)
WorkingHrs = WorkingHrs + IIf(CDbl(DayEnd - DayStart) < 0, 0, CDbl(DayEnd - DayStart))
End If
End If
Next

' convert days to hours
WorkingHrs = WorkingHrs * 24

Cleanup:
On Error GoTo 0
Set Dict = Nothing

End Function



To use it to calculate days:

WorkingHrs(Int([StartDateTime]), Int([EndDateTime]) + 1, 0, 1, 23456) / 24

You can supply additional arguments to specify holidays.

Patrick

[J_L]

It seems like you could just do a count of days minues the records in that range that appear in the Holidays recordset...

You calculate your datediff between beg/end and get the workdays okay, so you just need to take that number and subtract the COUNT of holiday dates that fall between beg and end date..

sqlHolidays = "Select Count(*) From [Holidays] Where Date between #" & BegDate & "# and #" & endDate & "#"

Which would give you a count of the holiday dates between begin and end dates passed... subtract that number from the other calculation....

perhaps?

So sorry how to i get the value from the sql statement? is there getInt() in vba? First time using vba.. So sorry..

[XLGibbs]

It may be easier to use Patricks User Defined Function's and his sameple formula.

You can also just put a query in the database that looks at the holiday table and does a count of all records, and takes parameters for the days from your form perhaps.

Don't make it more complicated that it has to be.