locked
Adding Business days to a start date, including holidays RRS feed

  • Question

  • User846546683 posted

    I read that .Net has no function equivalent to Excel's "Workdays" function, which adds a specified number of workdays to a startdate, including an array of holidays.  

    I managed to find a function that adds the number of workdays to the startdate, but I am having no luck telling the function to add a day to the start date for each holiday within the date range.

    I'm hoping that someone can point out what I'm doing wrong.  I've pasted a self-contained function below.  Any hints would be greatly appreciated.  Every time I compare the result against the Excel result, it's not adding up.

    Private Function CalcEndWD_5wkd(StartDate As Date, WD As Integer) As Date
                            
                Dim Holidays(23) As Date
                Holidays(0) = #10/5/2012#
                Holidays(1) = #11/12/2012#
                Holidays(2) = #11/21/2012#
                Holidays(3) = #11/22/2012#
                Holidays(4) = #11/23/2012#
                Holidays(5) = #12/17/2012#
                Holidays(6) = #12/18/2012#
                Holidays(7) = #12/19/2012#
                Holidays(8) = #12/20/2012#
                Holidays(9) = #12/21/2012#
                Holidays(10) = #12/22/2012#
                Holidays(11) = #12/23/2012#
                Holidays(12) = #12/24/2012#
                Holidays(12) = #12/25/2012#
                Holidays(11) = #12/26/2012#
                Holidays(12) = #12/27/2012#
                Holidays(13) = #12/28/2012#
                Holidays(14) = #12/29/2012#
                Holidays(15) = #12/30/2012#
                Holidays(16) = #12/31/2012#
                Holidays(17) = #1/1/2013#
                Holidays(18) = #1/21/2013#
                Holidays(19) = #2/21/2013#
                Holidays(20) = #2/22/2013#
                Holidays(21) = #3/8/2013#
                Holidays(22) = #3/29/2013#
                Holidays(23) = #4/26/2013#
                
                Dim index As Integer
                Dim x As Integer
                
                If StartDate.DayOfWeek = DayOfWeek.Saturday Or StartDate.DayOfWeek = DayOfWeek.Sunday Then
                    WD = WD - 1
                End If
                            
                For index = 0 To WD
                    Select Case StartDate.DayOfWeek
                        Case DayOfWeek.Sunday
                            StartDate = StartDate.AddDays(2)
                        Case DayOfWeek.Monday, DayOfWeek.Tuesday, DayOfWeek.Wednesday, DayOfWeek.Thursday, DayOfWeek.Friday
                            StartDate = StartDate.AddDays(1)
                        Case DayOfWeek.Saturday
                            StartDate = StartDate.AddDays(3)
                    End Select
    
                    For x = 0 To 23
                        If Holidays(x) = StartDate Then
                            StartDate= StartDate.AddDays(1)
                        End If
                    Next
                Next
    
                ' In case it lands on a holiday
                
                For x = 0 To 23
                    If Holidays(x) = StartDate Then
                        StartDate = StartDate.AddDays(1)
                    End If
                Next
                            
                If StartDate.DayOfWeek = DayOfWeek.Saturday Then
                    StartDate = StartDate.AddDays(2)
                ElseIf StartDate.DayOfWeek = DayOfWeek.Sunday Then
                    StartDate = StartDate.AddDays(1)
                End If
                           
                
                Return StartDate
            End Function



    Saturday, August 4, 2012 9:14 PM

Answers

  • User846546683 posted

    AHA!  While asp.net does not natively have any Excel functions, I was hit by a stray thought after a liquid break.  Is is possible to borrow an Excel function by means of a Namespace?

    The answer is yes.

    I am using Visual Studio, and added a project reference: Interop.Microsoft.Office.Interop.Excel.dll

    The trick is to create an Excel object and access the function through that Excel object.

    Dim xl As New Microsoft.Office.Interop.Excel.Application
     EndDate = Date.FromOADate(xl.WorksheetFunction.WorkDay(StartDate, WD, Holidays))

    Here is the code.

    Private Function CalcEndWD_5wkd(StartDate As Date, WD As Integer) As Date
                        
                Dim Holidays() As Date
                Holidays = New Date() {#10/5/2012#, #11/12/2012#, #11/21/2012#, #11/22/2012#, #11/23/2012#, #12/17/2012#, #12/18/2012#, #12/19/2012#, #12/20/2012#, #12/21/2012#, #12/22/2012#, #12/23/2012#, #12/24/2012#, #12/25/2012#, #12/26/2012#, #12/27/2012#, #12/28/2012#, #12/29/2012#, #12/30/2012#, #12/31/2012#, #1/1/2013#, #1/21/2013#, #2/21/2013#, #2/22/2013#, #3/8/2013#, #3/29/2013#, #4/26/2013#}
                            
                Dim EndDate As Date
                EndDate = StartDate
                
                Dim xl As New Microsoft.Office.Interop.Excel.Application
                EndDate = Date.FromOADate(xl.WorksheetFunction.WorkDay(StartDate, WD, Holidays))
                
                Return EndDate
                          
            End Function



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, August 5, 2012 1:06 AM

All replies

  • User846546683 posted

    I've adapted the code a little bit more, but it's still not matching Excel.

    I've even tried adding a separate function that loops through every single date in the array, and it doesn't work.

    Do While IsHoliday(EndDate) = True
       EndDate = EndDate.AddDays(1)
    Loop

    This is what both functions look like now.

    Private Function CalcEndWD_5wkd(StartDate As Date, WD As Integer) As Date

    Dim Holidays(23) As Date
    Holidays(0) = #10/5/2012#
    Holidays(1) = #11/12/2012#
    Holidays(2) = #11/21/2012#
    Holidays(3) = #11/22/2012#
    Holidays(4) = #11/23/2012#
    Holidays(5) = #12/17/2012#
    Holidays(6) = #12/18/2012#
    Holidays(7) = #12/19/2012#
    Holidays(8) = #12/20/2012#
    Holidays(9) = #12/21/2012#
    Holidays(10) = #12/22/2012#
    Holidays(11) = #12/23/2012#
    Holidays(12) = #12/24/2012#
    Holidays(12) = #12/25/2012#
    Holidays(11) = #12/26/2012#
    Holidays(12) = #12/27/2012#
    Holidays(13) = #12/28/2012#
    Holidays(14) = #12/29/2012#
    Holidays(15) = #12/30/2012#
    Holidays(16) = #12/31/2012#
    Holidays(17) = #1/1/2013#
    Holidays(18) = #1/21/2013#
    Holidays(19) = #2/21/2013#
    Holidays(20) = #2/22/2013#
    Holidays(21) = #3/8/2013#
    Holidays(22) = #3/29/2013#
    Holidays(23) = #4/26/2013#

    Dim index As Integer

    Dim EndDate As Date
    EndDate = StartDate

    If EndDate.DayOfWeek = DayOfWeek.Saturday Or EndDate.DayOfWeek = DayOfWeek.Sunday Then
    WD = WD - 1
    End If


    For index = 0 To WD
    Do While IsHoliday(EndDate) = True
    EndDate = EndDate.AddDays(1)
    Loop


    Select Case EndDate.DayOfWeek
    Case DayOfWeek.Sunday
    EndDate = EndDate.AddDays(2)
    Case DayOfWeek.Monday, DayOfWeek.Tuesday, DayOfWeek.Wednesday, DayOfWeek.Thursday, DayOfWeek.Friday
    EndDate = EndDate.AddDays(1)
    Case DayOfWeek.Saturday
    EndDate = EndDate.AddDays(3)
    End Select
    Next


    If EndDate.DayOfWeek = DayOfWeek.Saturday Then
    EndDate = EndDate.AddDays(2)
    ElseIf StartDate.DayOfWeek = DayOfWeek.Sunday Then
    EndDate = EndDate.AddDays(1)
    End If


    Return EndDate
    End Function

    Private Function IsHoliday(dt As Date) As Boolean
    Dim blnHoliday As Boolean = False


    Dim Holidays(23) As Date
    Holidays(0) = #10/5/2012#
    Holidays(1) = #11/12/2012#
    Holidays(2) = #11/21/2012#
    Holidays(3) = #11/22/2012#
    Holidays(4) = #11/23/2012#
    Holidays(5) = #12/17/2012#
    Holidays(6) = #12/18/2012#
    Holidays(7) = #12/19/2012#
    Holidays(8) = #12/20/2012#
    Holidays(9) = #12/21/2012#
    Holidays(10) = #12/22/2012#
    Holidays(11) = #12/23/2012#
    Holidays(12) = #12/24/2012#
    Holidays(12) = #12/25/2012#
    Holidays(11) = #12/26/2012#
    Holidays(12) = #12/27/2012#
    Holidays(13) = #12/28/2012#
    Holidays(14) = #12/29/2012#
    Holidays(15) = #12/30/2012#
    Holidays(16) = #12/31/2012#
    Holidays(17) = #1/1/2013#
    Holidays(18) = #1/21/2013#
    Holidays(19) = #2/21/2013#
    Holidays(20) = #2/22/2013#
    Holidays(21) = #3/8/2013#
    Holidays(22) = #3/29/2013#
    Holidays(23) = #4/26/2013#

    Dim x As Integer

    For x = 0 To 23
    If dt = Holidays(x) Then blnHoliday = True
    Next

    Return blnHoliday
    End Function



    Saturday, August 4, 2012 11:06 PM
  • User846546683 posted

    It's still landing on a holiday defined in the noliday array.

    I figured the "run loop again and again so long as enddate is still a holiday" would do it, but for some reason it's not working.

    Saturday, August 4, 2012 11:33 PM
  • User846546683 posted

    I refuse to believe that it is impossible to replicate the Excel Worddays function with holidays, but I simply cannot replicate it.

    I cannot believe that Microsoft has not felt it necessary to extend this function to .Net when there is clearly a need for this type of function.

    Saturday, August 4, 2012 11:42 PM
  • User846546683 posted

    AHA!  While asp.net does not natively have any Excel functions, I was hit by a stray thought after a liquid break.  Is is possible to borrow an Excel function by means of a Namespace?

    The answer is yes.

    I am using Visual Studio, and added a project reference: Interop.Microsoft.Office.Interop.Excel.dll

    The trick is to create an Excel object and access the function through that Excel object.

    Dim xl As New Microsoft.Office.Interop.Excel.Application
     EndDate = Date.FromOADate(xl.WorksheetFunction.WorkDay(StartDate, WD, Holidays))

    Here is the code.

    Private Function CalcEndWD_5wkd(StartDate As Date, WD As Integer) As Date
                        
                Dim Holidays() As Date
                Holidays = New Date() {#10/5/2012#, #11/12/2012#, #11/21/2012#, #11/22/2012#, #11/23/2012#, #12/17/2012#, #12/18/2012#, #12/19/2012#, #12/20/2012#, #12/21/2012#, #12/22/2012#, #12/23/2012#, #12/24/2012#, #12/25/2012#, #12/26/2012#, #12/27/2012#, #12/28/2012#, #12/29/2012#, #12/30/2012#, #12/31/2012#, #1/1/2013#, #1/21/2013#, #2/21/2013#, #2/22/2013#, #3/8/2013#, #3/29/2013#, #4/26/2013#}
                            
                Dim EndDate As Date
                EndDate = StartDate
                
                Dim xl As New Microsoft.Office.Interop.Excel.Application
                EndDate = Date.FromOADate(xl.WorksheetFunction.WorkDay(StartDate, WD, Holidays))
                
                Return EndDate
                          
            End Function



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, August 5, 2012 1:06 AM