Workday

[Bruce Fox]

Hi All,

I needed a function similar to Excels WORKDAY to provide a Date in advance excluding Weekends (and Holidays [Optional]).

I tried a few methods, and ended up with this.

Simply build a list of Dates and iterate them removing Weekends and Holidays, then pick the number of days ahead. It is useful for forecasting graduation dates for a given course of length n. (Note, it is Aus centric)

Please comment

Option Explicit On
Option Strict On

    Public Function FindDate(ByVal startdate As Date, ByVal days As Integer, Optional ByVal holidays As List(Of Date) = Nothing) As Date
        ' *****************************************************
        ' Validate
        If days < 1 Then Err.Raise(vbObjectError + 513, "Module.ForcastDate.Limit", "Minimum number of 'days' exceeded. The allowed lower limit is 1.")
        If days > 2000 Then Err.Raise(vbObjectError + 2000, "Module.ForcastDate.Limit", "Maximum number of 'days' exceeded. The allowed maximum limit is 2000.")
        ' *****************************************************

        Dim dates As New List(Of Date)

        ' We need to ensure that enough Dates exist AFTER the Weekends and Holidays are removed so the 'days' ahead will hit a Date and not error out on 'Index out Of Range'
        ' Therefore, pad the offset out using the (arbitrary) functions below
        Dim offset As Integer
        If days < 6 Then
            offset = days + Convert.ToInt16(days * 2)
        Else
            offset = days + Convert.ToInt16(days * 0.5)
        End If
        If Not IsNothing(holidays) Then offset += Convert.ToInt16(holidays.Count * 2)

        Try
            For intCount = 0 To offset - 1 ' Include the startdate as the point to calculate from by indexing as "0 to n" days - 1
                dates.Add(DateAdd(DateInterval.Day, intCount, startdate).Date)
            Next
            ' Use LINQ to remove Weekends
            For Each _date In dates.Where(Function(i) i.Date.DayOfWeek = DayOfWeek.Saturday Or i.Date.DayOfWeek = DayOfWeek.Sunday).ToArray()
                dates.Remove(_date)
            Next
            ' Now, remove all Holidays
            If Not IsNothing(holidays) Then
                For Each _holiday In holidays
                    dates.Remove(_holiday.Date)
                Next
            End If

            Return dates.Item(days - 1)

        Catch ex As Exception
            ' Return an arbitary Date
            Return #1/1/1900#
        End Try

    End Function

Use:

        Dim workday As New clsWorkday

        Dim holidaylist As New List(Of Date)
        holidaylist.Add(Convert.ToDateTime("11/7/2012"))
        holidaylist.Add(Convert.ToDateTime("12/7/2012"))
        Try
            MessageBox.Show(FindDate(Convert.ToDateTime("09/7/2012").Date, 4, holidaylist).ToShortDateString)
           MessageBox.Show(FindDate(Convert.ToDateTime("09/7/2012").Date, 160).ToShortDateString)
        Catch ex As Exception
            MsgBox(ex.Message)
            MsgBox(ex.Source)
        End Try