• ### 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
Case DayOfWeek.Monday, DayOfWeek.Tuesday, DayOfWeek.Wednesday, DayOfWeek.Thursday, DayOfWeek.Friday
Case DayOfWeek.Saturday
End Select

For x = 0 To 23
If Holidays(x) = StartDate Then
End If
Next
Next

' In case it lands on a holiday

For x = 0 To 23
If Holidays(x) = StartDate Then
End If
Next

If StartDate.DayOfWeek = DayOfWeek.Saturday Then
ElseIf StartDate.DayOfWeek = DayOfWeek.Sunday Then
End If

Return StartDate
End Function```

Saturday, August 4, 2012 9:14 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?

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

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

Return EndDate

End Function```

• Marked as answer by 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
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
Loop

Select Case EndDate.DayOfWeek
Case DayOfWeek.Sunday
Case DayOfWeek.Monday, DayOfWeek.Tuesday, DayOfWeek.Wednesday, DayOfWeek.Thursday, DayOfWeek.Friday
Case DayOfWeek.Saturday
End Select
Next

If EndDate.DayOfWeek = DayOfWeek.Saturday Then
ElseIf StartDate.DayOfWeek = DayOfWeek.Sunday Then
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?

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

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