# calculate hours between two dates but only business hours

• ### 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
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

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

Monday, March 4, 2019 8:45 PM
• 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"))
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

```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
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

Tuesday, March 5, 2019 1:30 PM
• 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()
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
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)

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

Tuesday, March 5, 2019 3:08 PM
• 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
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
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

Tuesday, March 5, 2019 5:33 PM