none
calculate days between two dates excluding weekends RRS feed

  • 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 wingers Monday, March 4, 2019 9:02 PM edited title
    Monday, March 4, 2019 3:33 PM

Answers

  • 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
            pStart = pStart.AddDays(1)
        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
    profile for Karen Payne on Stack Exchange

    • Marked as answer by wingers Monday, March 4, 2019 5:43 PM
    Monday, March 4, 2019 4:03 PM
    Moderator
  • 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
                pStart = pStart.AddDays(1)
            Loop
            Return days
        End Function




    • Edited by wingers Monday, March 4, 2019 4:56 PM edit - fixed I think
    • Marked as answer by wingers 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
            pStart = pStart.AddDays(1)
        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
    profile for Karen Payne on Stack Exchange

    • Marked as answer by wingers Monday, March 4, 2019 5:43 PM
    Monday, March 4, 2019 4:03 PM
    Moderator
  • 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
    profile for Karen Payne on Stack Exchange

    Monday, March 4, 2019 4:33 PM
    Moderator
  • 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
                pStart = pStart.AddDays(1)
            Loop
            Return days
        End Function




    • Edited by wingers Monday, March 4, 2019 4:56 PM edit - fixed I think
    • Marked as answer by wingers 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
    profile for Karen Payne on Stack Exchange

    Monday, March 4, 2019 5:04 PM
    Moderator
  • 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
  • Wingers, please mark your threads as answered so we can stop wasting time clicking and reading. Thanks

    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
  • Wingers, please mark your threads as answered so we can stop wasting time clicking and reading. Thanks

    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
  • Monday, March 4, 2019 6:34 PM