How to calculate business hours between 2 dates excluding weekends

Question

I created VB function to calculate hours between 2 dates excluding weekends. Now I would have to consider "operating hours" -
Use case:
Helpdesk ticket was opened on date_opened
and replied on last_updated date
I would need to calculate TurnAroundTime considering  Operating hours
Scenario 1: 9am  - 12 am Mon-Sun - 7 days
Scenario 2: 9 am - 6 pm Mon - Fri - excluding Sat,Sun 

The output should be number of hours passed between these 2 dates (for example,  24 hours).

How should I adjust VB code to accomodate operationg hours? Please help.

Public Shared Function HoursUsed(ByVal date_opened As Date, ByVal last_updated As Date)
Public Shared Function HoursUsed(ByVal date_opened As Date, ByVal last_updated As Date) AS Integer
Dim DaysPassed As Integer = DateDiff(DateInterval.Hour,date_opened,last_updated)
Dim SaturdaysPassed As Integer = DateDiff(DateInterval.WeekOfYear,date_opened,last_updated, FirstDayOfWeek.Sunday)
Dim SundaysPassed As Integer = DateDiff(DateInterval.WeekOfYear,date_opened,last_updated, FirstDayOfWeek.Monday)
Dim HoursPassed As Integer = (DaysPassed - SaturdaysPassed*24 - SundaysPassed*24)
return HoursPassed
End Function

Answer 1

Ulka,

this example might help you get it together

Sub calculatedates()
    Dim startdate As Date: startdate = CDate("10/29/2009")
    Dim enddate As Date: enddate = Format(Now, "yyyy/mm/dd")
    Dim NumDaysInRange As Integer: NumDaysInRange = DateDiff("d", startdate, enddate)
    For i = 0 To NumDaysInRange
        Dim workingdate As Date: workingdate = startdate + i
        Dim d As Integer: d = Weekday(workingdate, vbMonday)
        If d < 6 Then
            'this is a weekday
            'MsgBox ("weekday")
        Else
            'this is a weekend day
            'MsgBox ("weekend")
       End If
    Next
End Sub



how it works is:
get the number of days in between the two dates
loop the number of days including the first day
checks the number value of the day as it pertains to the day in the week with the first day starting monday
    so monday would be day 1, tuesday day 2, etc...
    if the day number is less than 6 (saturday) then it is a weekday, else it is a weekend day.


you can use the above to create a running total of weekdays for your scenario #2.  if it is a weekday then add 1 to a total count of weekdays and once finished with the loop you can use the total weekday count to multiply a days hours by.  or you can just add the day's hours to a total variable in the loop.  however works for you.  basically just take the hours for one day and multiply it on the number of weekdays found in the range.  since you only need weekdays you can disregard the else in the if check on the day of the week.

hope this helps you get it all sorted


also, if you don't mind me asking:  i am adding some new features to my website for allowing members to upload projects to get help from other members.  many times members ask to email me or others, their projects, so this would help a lot of forum members.  is this something you think might be of interest to you for future projects?

---

FREE DEVELOPER TOOLS, CODE & PROJECTS at www.srsoft.us Database Code Generator and Tutorial

Answer 2

I found the solutions - here is the function that inputs startdate(ticket created), enddate(ticket repsponded), operating hour start and end(24 clock) and weekend flag (1 - if weekends excluded)

Public Shared Function HoursUsed(ByVal startDate As Date, ByVal endDate As Date, ByVal busStart As Long, ByVal busEnd As Long, ByVal weekendFlag As Integer) As Long
        Dim startDate_hn As Date
        Dim HoursPassed0 As Long
        Dim startDate_hwn As Date
        Select Case weekendFlag
            Case 0
                If CInt(DatePart(DateInterval.Hour, startDate)) < busStart Then
                    startDate_hn = DateAdd(DateInterval.hour, busStart, DATEADD(DateInterval.day , DATEDIFF(DateInterval.day ,Date.MinValue,startDate),Date.MinValue))
                ElseIf CInt(DatePart(DateInterval.Hour, startDate)) > busEnd Then
                    startDate_hn = DateAdd(DateInterval.hour, busStart, DATEADD(DateInterval.day , 1+DATEDIFF(DateInterval.day ,Date.MinValue,startDate),Date.MinValue))
                Else : startDate_hn = startDate
                End If
                HoursPassed0 = CInt(DateDiff(DateInterval.Hour, startDate_hn, endDate))
If HoursPassed0 < 0 Then HoursPassed0 = 0
Return HoursPassed0
            Case 1
                   If CInt(DatePart(DateInterval.Hour, startDate)) < busStart Then
                    startDate_hn = DateAdd(DateInterval.hour, busStart, DATEADD(DateInterval.day , DATEDIFF(DateInterval.day ,Date.MinValue,startDate),Date.MinValue))
                ElseIf CInt(DatePart(DateInterval.Hour, startDate)) > busEnd Then
                    startDate_hn = DateAdd(DateInterval.hour, busStart, DATEADD(DateInterval.day , 1+DATEDIFF(DateInterval.day ,Date.MinValue,startDate),Date.MinValue))
                Else : startDate_hn = startDate
                End If
                    If CInt(DatePart(DateInterval.Weekday, startDate_hn)) = 7 Then
                        startDate_hwn = DateAdd(DateInterval.hour, busStart, DATEADD(DateInterval.day , 2+DATEDIFF(DateInterval.day ,Date.MinValue,startDate_hn),Date.MinValue))
                    ElseIf CInt(DatePart(DateInterval.Weekday, startDate_hn)) = 1 Then
                        startDate_hwn = DateAdd(DateInterval.hour, busStart, DATEADD(DateInterval.day , 1+DATEDIFF(DateInterval.day ,Date.MinValue,startDate_hn),Date.MinValue))
                    Else : startDate_hwn = startDate_hn
                    End If
                Dim HoursPassed As Long = CInt(DateDiff(DateInterval.Hour, startDate_hwn, endDate))
If HoursPassed < 0 Then HoursPassed = 0
                Dim SaturdaysPassed As Long = DateDiff(DateInterval.WeekOfYear, startDate_hwn, endDate, FirstDayOfWeek.Sunday)
                Dim SundaysPassed As Long = DateDiff(DateInterval.WeekOfYear, startDate_hwn, endDate, FirstDayOfWeek.Monday)
                Dim HoursPassed1 As Long = (HoursPassed - SaturdaysPassed * 24 - SundaysPassed * 24)
                Return HoursPassed1
        End Select
    End Function