none
calculate hours between two dates but only business hours RRS feed

  • Question

  • Does anyone have a function to the following:-

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

    This is to calculate SLA for help desk

    So will have start_datetime and end_datetime


    Darren Rose

    Monday, March 4, 2019 6:34 PM

All replies

  • Hi Darren,

    See if this is what you are looking for, note I didn't do the holiday part as the focuses on day number and time.

    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Dim startDate = #3/1/2019#
            Dim endDate = #3/8/2019#
    
            Dim startTime = "9:00"
            Dim endTime = "17:00"
    
            Dim duration As TimeSpan = Date.Parse(endTime).Subtract(Date.Parse(startTime))
    
    
            Dim workDays = GetNumberOfWorkingDays(startDate, endDate)
            Console.WriteLine(workDays * duration.Hours)
        End Sub
        ''' <summary>
        ''' Yep it's missing holidays 
        ''' </summary>
        ''' <param name="pStart"></param>
        ''' <param name="pStop"></param>
        ''' <returns></returns>
        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
    
    End Class


    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 7:18 PM
    Moderator
  • Hi

    No that doesn't get me expected result

    For example if I use

    startdate of "01/03/19 16:00"

    enddate of "04/03/19 10:00"

    then it is returning 8 hours using above code

    But should be returning 2 hours as on Friday 01/03 16:00 to 17:00 = 1 hour then next 2 days are weekend, and then on 04/03 09:00 to 10:00 is 1 hour  = total of 2 hours working time


    Darren Rose

    Monday, March 4, 2019 8:04 PM
  • Just so you know I'm not ignoring this I'm currently in a side-by-side session coding at work session.

    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 8:45 PM
    Moderator
  • Just so you know I'm not ignoring this I'm currently in a side-by-side session coding at work session.


    that's fine, no hurry, I appreciate your help whenever :)

    Darren Rose

    Monday, March 4, 2019 8:48 PM
  • Hi,

    try the code:

    Module Module1
    
        Sub Main()
            Console.WriteLine(gethours("2019-03-05 14:00", "2019-03-05 17:00"))
            Console.ReadLine()
        End Sub
        Public Function gethours(ByVal starttime As String, ByVal endtime As String) As Integer
            Dim start As DateTime = Convert.ToDateTime(starttime)
            Dim [end] As DateTime = Convert.ToDateTime(endtime)
            Dim span As TimeSpan = [end] - start
            Dim AllDays As Integer = Convert.ToInt32(span.TotalDays) + 1
            Dim totleWeek As Integer = AllDays / 7
            Dim yuDay As Integer = AllDays Mod 7
            Dim lastDay As Integer = 0
    
            If yuDay = 0 Then
                lastDay = AllDays - (totleWeek * 2)
            Else
                Dim weekDay As Integer = 0
                Dim endWeekDay As Integer = 0
    
                Select Case start.DayOfWeek
                    Case DayOfWeek.Monday
                        weekDay = 1
                    Case DayOfWeek.Tuesday
                        weekDay = 2
                    Case DayOfWeek.Wednesday
                        weekDay = 3
                    Case DayOfWeek.Thursday
                        weekDay = 4
                    Case DayOfWeek.Friday
                        weekDay = 5
                    Case DayOfWeek.Saturday
                        weekDay = 6
                    Case DayOfWeek.Sunday
                        weekDay = 7
                End Select
    
                If (weekDay = 6 AndAlso yuDay >= 2) OrElse (weekDay = 7 AndAlso yuDay >= 1) OrElse (weekDay = 5 AndAlso yuDay >= 3) OrElse (weekDay = 4 AndAlso yuDay >= 4) OrElse (weekDay = 3 AndAlso yuDay >= 5) OrElse (weekDay = 2 AndAlso yuDay >= 6) OrElse (weekDay = 1 AndAlso yuDay >= 7) Then
                    endWeekDay = 2
                End If
    
                If (weekDay = 6 AndAlso yuDay < 1) OrElse (weekDay = 7 AndAlso yuDay < 5) OrElse (weekDay = 5 AndAlso yuDay < 2) OrElse (weekDay = 4 AndAlso yuDay < 3) OrElse (weekDay = 3 AndAlso yuDay < 4) OrElse (weekDay = 2 AndAlso yuDay < 5) OrElse (weekDay = 1 AndAlso yuDay < 6) Then
                    endWeekDay = 1
                End If
    
                lastDay = AllDays - (totleWeek * 2) - endWeekDay
            End If
            Return (lastDay - 1) * 8 + (17 - Convert.ToDateTime(starttime).Hour) + (Convert.ToDateTime(endtime).Hour - 9)
        End Function
    End Module
    

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 5, 2019 3:22 AM
  • Hi Alex

    That seems to give me an odd result - using same example I have used for other tests

     MsgBox(gethours("01/03/19 16:00", "04/03/19 10:00"))

    gives me -6    - but should be 2 as per reply to Karen above

    This one

    MsgBox(gethours("01/03/19 10:00", "01/03/19 18:00"))

    gives me 8    - but should be 7 as working hours 09:00 to 17:00


    Darren Rose

    Tuesday, March 5, 2019 11:28 AM
  • Think I have found working solution 

    From - https://stackoverflow.com/questions/5005983/determine-the-difference-between-two-datetimes-only-counting-opening-hours

    Public Function WorkingMinutes(ByVal startdate As Date, ByVal enddate As Date) As Long
    
            Dim count As Integer = 0
    
            Dim i = startdate
            Do While i < enddate
                If i.DayOfWeek <> DayOfWeek.Saturday AndAlso i.DayOfWeek <> DayOfWeek.Sunday Then
    
                    If i.TimeOfDay.Hours >= 9 AndAlso i.TimeOfDay.Hours < 17 Then
                        count += 1
                    End If
                End If
                i = i.AddMinutes(1)
            Loop
    
            Return count
    
        End Function



    Darren Rose

    Tuesday, March 5, 2019 12:30 PM
  • just need to work out how to encompass ignoring specified holiday days as well as weekends like done here when calculating days between two dates

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/b57d5fcb-6033-4df3-b78e-50e0e350677e/calculate-days-between-two-dates-excluding-weekends?forum=vbgeneral


    Darren Rose

    Tuesday, March 5, 2019 12:31 PM
  • Hello Darren,

    Had not gotten much time to spend on this but did come up with the following which may be of possible usage in that you would parse each day's time spend and do a running total but than it may not be of use.

    End time could be fixed to say 5:00 PM.

    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) _
            Handles Button1.Click
    
            Dim startDate = New DateTime(2019, 3, 3, 13, 0, 0)
            Dim endDate = New DateTime(2019, 3, 3, 17, 0, 0)
    
            Dim duration As TimeSpan =
                    Date.Parse(endDate.ToString("hh:mm tt")).Subtract(
                        Date.Parse(startDate.ToString("hh:mm tt")))
    
            Console.WriteLine($"{duration} - {duration.Hours}")
    
        End Sub
    End Class
    


    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

    Tuesday, March 5, 2019 1:30 PM
    Moderator
  • Thanks Karen - think one I found and posted above seems to do the job, just need to get it to skip holidays as well and then all sorted

    Darren Rose

    Tuesday, March 5, 2019 2:00 PM
  • Perhaps looking back on weekends and holidays

    Public Class DateUtils
        Public Sub New()
            SetHolidays()
        End Sub
        Public Holidays As New List(Of Date)
    
        Public Sub SetHolidays()
            Holidays.Add(New Date(Now.Year, 1, 1))
            Holidays.Add(New Date(Now.Year, 1, 5))
            Holidays.Add(New Date(Now.Year, 1, 21))
            Holidays.Add(New Date(Now.Year, 2, 18))
            Holidays.Add(New Date(Now.Year, 4, 21))
            Holidays.Add(New Date(Now.Year, 5, 27))
            Holidays.Add(New Date(Now.Year, 7, 4))
            Holidays.Add(New Date(Now.Year, 9, 2))
            Holidays.Add(New Date(Now.Year, 11, 11))
            Holidays.Add(New Date(Now.Year, 11, 28))
            Holidays.Add(New Date(Now.Year, 12, 25))
            Holidays.Add(New Date(Now.Year, 12, 31))
        End Sub
        Public Function IsHoliday(pDate As Date) As Boolean
            Return Holidays.Contains(pDate)
        End Function
        Public Function IsWeekEnd(pDate As Date) As Boolean
            Return pDate.DayOfWeek =
                   DayOfWeek.Saturday OrElse pDate.DayOfWeek = DayOfWeek.Sunday
        End Function
        Public Function GetNextWorkingDay(pDate As Date) As Date
            Do
                pDate = pDate.AddDays(1)
            Loop While IsHoliday(pDate) OrElse IsWeekEnd(pDate)
    
            Return pDate
    
        End Function
    End Class
    

    Using the above in a hard code fashion.

    Dim ops = New DateUtils
    
    Dim monthIndex = 12
    
    For dayIndex As Integer = 1 To Date.DaysInMonth(Now.Year, monthIndex)
    
        Dim dt = New Date(Date.Now.Year, monthIndex, dayIndex)
        Dim isWeekend = ops.IsWeekEnd(dt)
        Dim isHoliday = ops.IsHoliday(dt)
    
        DataGridView1.Rows.Add(dt, isWeekend, isHoliday, ops.GetNextWorkingDay(dt))
    
    Next


    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

    Tuesday, March 5, 2019 3:08 PM
    Moderator
  • Thanks Karen - that is very useful

    So how can I utilize this for the working hours part so that also doesn't include weekends or holidays?

    This was code I had working which did hours fine but only excluded weekends

    Public Function WorkingMinutes(ByVal startdate As Date, ByVal enddate As Date) As Long
    
            Dim count As Integer = 0
    
            Dim i = startdate
            Do While i < enddate
                If i.DayOfWeek <> DayOfWeek.Saturday AndAlso i.DayOfWeek <> DayOfWeek.Sunday Then
    
                    If i.TimeOfDay.Hours >= 9 AndAlso i.TimeOfDay.Hours < 17 Then
                        count += 1
                    End If
                End If
                i = i.AddMinutes(1)
            Loop
    
            Return count
    
        End Function

    I am assuming this change will work, but not in front of computer until later:-

    Public Function WorkingMinutes(ByVal startdate As Date, ByVal enddate As Date) As Long
    
            Dim count As Integer = 0
    
            Dim i = startdate
            Do While i < enddate
                if IsWeekend(i) = False AndAlso IsHoliday(i) = False Then
                    If i.TimeOfDay.Hours >= 9 AndAlso i.TimeOfDay.Hours < 17 Then
                        count += 1
                    End If
                End If
                i = i.AddMinutes(1)
            Loop
    
            Return count
    
        End Function


    Darren Rose

    Tuesday, March 5, 2019 5:30 PM
  • Once again I need to wait till later as I'm back doing side by side development again today so only have time for quickie replies.

    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

    Tuesday, March 5, 2019 5:33 PM
    Moderator