# calculate days between two dates excluding weekends

• ### Question

• Does anyone have a function to d the following:-

Calculate number of days between two dates but excluding weekends (and possibly bank holidays)

This is to calculate SLA for help desk

So will have start_datetime and end_datetime

Darren Rose

• Edited by Monday, March 4, 2019 9:02 PM edited title
Monday, March 4, 2019 3:33 PM

• Hello Darren,

Two things would be needed a function for excluding weekends and container (e.g. text file, xml file, database table etc) that contains bank holidays

```Private Function GetNumberOfWorkingDays(pStart As Date, pStop As Date) As Integer
Dim days As Integer = 0
Do While pStart <= pStop
If pStart.DayOfWeek <> DayOfWeek.Saturday AndAlso pStart.DayOfWeek <> DayOfWeek.Sunday Then
days += 1
End If
Loop
Return days
End Function```

Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

NuGet BaseConnectionLibrary for database connections.

StackOverFlow

• Marked as answer by Monday, March 4, 2019 5:43 PM
Monday, March 4, 2019 4:03 PM
• Fair point, but as these are bank holidays they won't change so should be fine, do you agree with how I have amended your code to check those as well? is that best way

``` Private Function GetNumberOfWorkingDays(pStart As Date, pStop As Date) As Integer
Dim days As Integer = 0
Do While pStart <= pStop
If pStart.DayOfWeek <> DayOfWeek.Saturday AndAlso pStart.DayOfWeek <> DayOfWeek.Sunday Then
If Not BankHolidays.Contains(pStart.Date) Then
days += 1
End If
End If
Loop
Return days
End Function```

• Edited by Monday, March 4, 2019 4:56 PM edit - fixed I think
• Marked as answer by Monday, March 4, 2019 5:43 PM
Monday, March 4, 2019 4:49 PM

### All replies

• Hello Darren,

Two things would be needed a function for excluding weekends and container (e.g. text file, xml file, database table etc) that contains bank holidays

```Private Function GetNumberOfWorkingDays(pStart As Date, pStop As Date) As Integer
Dim days As Integer = 0
Do While pStart <= pStop
If pStart.DayOfWeek <> DayOfWeek.Saturday AndAlso pStart.DayOfWeek <> DayOfWeek.Sunday Then
days += 1
End If
Loop
Return days
End Function```

Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

NuGet BaseConnectionLibrary for database connections.

StackOverFlow

• Marked as answer by Monday, March 4, 2019 5:43 PM
Monday, March 4, 2019 4:03 PM
• Thanks Karen

As not many holiday dates I was thinking something like this to store the dates:-

`Dim BankHolidays As New List(Of Date)({"01/01/2019", "19/04/2019", "22/04/2019", "06/05/2019", "27/05/2019", "26/08/2019", "25/12/2019", "26/12/2019"})`

Darren Rose

Monday, March 4, 2019 4:13 PM
• Thanks Karen

As not many holiday dates I was thinking something like this to store the dates:-

`Dim BankHolidays As New List(Of Date)({"01/01/2019", "19/04/2019", "22/04/2019", "06/05/2019", "27/05/2019", "26/08/2019", "25/12/2019", "26/12/2019"})`

Darren Rose

That would work. The reason I mentioned a container is I like to be able to change things without a re-compile.

Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

NuGet BaseConnectionLibrary for database connections.

StackOverFlow

Monday, March 4, 2019 4:33 PM
• Fair point, but as these are bank holidays they won't change so should be fine, do you agree with how I have amended your code to check those as well? is that best way

``` Private Function GetNumberOfWorkingDays(pStart As Date, pStop As Date) As Integer
Dim days As Integer = 0
Do While pStart <= pStop
If pStart.DayOfWeek <> DayOfWeek.Saturday AndAlso pStart.DayOfWeek <> DayOfWeek.Sunday Then
If Not BankHolidays.Contains(pStart.Date) Then
days += 1
End If
End If
Loop
Return days
End Function```

• Edited by Monday, March 4, 2019 4:56 PM edit - fixed I think
• Marked as answer by Monday, March 4, 2019 5:43 PM
Monday, March 4, 2019 4:49 PM
• Looks good to me. Only true way to ensure this works is to write a unit test(s).

Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

NuGet BaseConnectionLibrary for database connections.

StackOverFlow

Monday, March 4, 2019 5:04 PM
• Looks good to me. Only true way to ensure this works is to write a unit test(s).

thanks - have run it multiple times with lots of dates including holiday periods and it seems fine

now onto second requirement

"Calculate number of hours between two dates but only including business hours of 09:00 - 17:00"

Darren Rose

Monday, March 4, 2019 5:07 PM

Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

Monday, March 4, 2019 5:39 PM

Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

I will quite happily do so when "both points" I asked have answers!.......

Darren Rose

Monday, March 4, 2019 5:42 PM
• split second part of question to new topic as pressured to close this and mark replies...

Darren Rose

Monday, March 4, 2019 6:34 PM