Get Working Hours using .NET

At work there was a requirement to count working hours from an arbitrary event to the current time. At first I tried to do this with sql, using this as a starting point, but the processing required was too intensive. The alternative was to work it out in code. With the help of this post I came up with the following vb with the judicious use of the structure System.Timespan to get the number of hours. The start point is the function NumberOfWorkingHours :

Public Function NumberOfWorkingHours(ByVal dtStartDate As DateTime, ByVal dtEndDate As DateTime) As Integer

Dim intWorkingHours As Integer

Dim boolSameDate As Boolean = False

Dim intNumberOfWorkingHoursPerDay As Integer = 8
Dim strStartTime As String = "09:00:00"

' set both dates to midnight
Dim dtTempStart As DateTime = CDate(dtStartDate.ToShortDateString)
Dim dtTempEnd As DateTime = CDate(dtEndDate.ToShortDateString)

' check if its the same day
If (dtTempStart = dtTempEnd) Then
intWorkingHours = Math.Floor((LastDayNumberOfWorkingSeconds(dtTempEnd, strStartTime, intNumberOfWorkingHoursPerDay) / 60 / 60))
Else
' get number of hours for first day
intWorkingHours = Math.Floor((FirstDayNumberOfWorkingSeconds(dtStartDate, strStartTime, intNumberOfWorkingHoursPerDay) / 60 / 60))
' get number of hours for last day
intWorkingHours += Math.Floor((LastDayNumberOfWorkingSeconds(dtEndDate, strStartTime, intNumberOfWorkingHoursPerDay) / 60 / 60))
' get number of hours of full Working days between two dates
intWorkingHours += (WorkingDaysExcludeWeekendsAndHolidays(dtTempStart.AddDays(1), dtTempEnd.AddDays(-1)) * intNumberOfWorkingHoursPerDay)

End If

Return intWorkingHours
End Function

Private Function WorkingDaysExcludeWeekendsAndHolidays _
(ByVal dtStartDate As Date, ByVal dtStopDate As Date) As Integer

Dim intActualDays As Integer
Dim intWorkingDays As Integer
Dim dtNextDate As Date
Dim x As Double

If dtStopDate 0 Then
intWorkingDays = intWorkingDays - 1
End If
Next

Return intWorkingDays

End Function

Private Function FirstDayNumberOfWorkingSeconds(ByVal dt As DateTime, ByVal strStartTime As String, ByVal intWorkingHoursPerDay As Integer) As String
Dim tsTimeSpan As TimeSpan
Dim intReturnValue As Integer = 0

Dim dtMinTime As DateTime = dt.ToShortDateString & " " & strStartTime
Dim dtMaxTime As DateTime = dtMinTime.AddHours(intWorkingHoursPerDay)

' Response.Write("DateTime entered:" & dt.ToString() & "
")
' Response.Write("Start date time:" & dtMinTime.ToString() & "
")
' Response.Write("End date time:" & dtMaxTime.ToString() & "
")

If (dtMaxTime < dt) Then ' start time is after Working close time

intReturnValue = 0
Else

'If dt.DayOfWeek = System.DayOfWeek.Saturday Or dt.DayOfWeek = System.DayOfWeek.Sunday Then

If DayIsWeekendDayOrBankHoliday(dt) Then

intReturnValue = 0

Else
If (dt < dtMinTime) Then ' start time is before Working start time

dt = dtMinTime

End If

tsTimeSpan = dtMaxTime.Subtract(dt)
' intReturnValue = tsTimeSpan.Hours
intReturnValue = CInt(Math.Floor(tsTimeSpan.Ticks / TimeSpan.TicksPerSecond))

End If

End If

Return intReturnValue

End Function

Private Function LastDayNumberOfWorkingSeconds(ByVal dt As DateTime, ByVal strStartTime As String, ByVal intWorkingHoursPerDay As Integer) As String
Dim tsTimeSpan As TimeSpan
Dim intReturnValue As Integer = 0

Dim dtMinTime As DateTime = dt.ToShortDateString & " " & strStartTime
Dim dtMaxTime As DateTime = dtMinTime.AddHours(intWorkingHoursPerDay)

'Response.Write("DateTime entered:" & dt.ToString() & "
")
'Response.Write("Start date time:" & dtMinTime.ToString() & "
")
'Response.Write("End date time:" & dtMaxTime.ToString() & "
")

If (dtMinTime > dt) Then ' end time is before Working start time

intReturnValue = 0
Else

If DayIsWeekendDayOrBankHoliday(dt) Then

intReturnValue = 0

Else
If (dt > dtMaxTime) Then ' end time is after Working end time

dt = dtMaxTime

End If

tsTimeSpan = dt.Subtract(dtMinTime)
' intReturnValue = tsTimeSpan.Hours
intReturnValue = CInt(Math.Floor(tsTimeSpan.Ticks / TimeSpan.TicksPerSecond))

End If

End If

Return intReturnValue

End Function

Private Function DayIsWeekendDayOrBankHoliday(ByVal dt As DateTime) As Boolean
dt = CDate(dt.ToShortDateString)
If dt.DayOfWeek = DayOfWeek.Saturday Or DayOfWeek.Sunday Then
Return True
Else
For Each dtBankHoliday As Date In ReturnBankHolidays()
If dtBankHoliday = dt Then
Return True
End If
Next
Return False
End If
End Function

Private Function ReturnBankHolidays() As Array

Dim BankHolidays(32) As Date
'2009
BankHolidays(0) = #1/1/2009# 'New Year's Day
BankHolidays(1) = #4/10/2009# 'Good Friday
BankHolidays(2) = #4/13/2009# 'Easter Monday
BankHolidays(3) = #5/4/2009# 'May Bank Holiday
BankHolidays(4) = #5/25/2009# 'Spring Bank Holiday
BankHolidays(5) = #8/31/2009# 'Summer Bank Holiday
BankHolidays(6) = #12/25/2009# 'Christmas Day
BankHolidays(7) = #12/28/2009# 'Boxing Day * sub day
'2010
BankHolidays(8) = #1/1/2010# 'New Year's Day
BankHolidays(9) = #4/2/2010# 'Good Friday
BankHolidays(10) = #4/5/2010# 'Easter Monday
BankHolidays(11) = #5/3/2010# 'May Bank Holiday
BankHolidays(12) = #5/31/2010# 'Spring Bank Holiday
BankHolidays(13) = #8/30/2010# 'Summer Bank Holiday
BankHolidays(14) = #12/27/2010# 'Christmas Day * sub day
BankHolidays(15) = #12/28/2010# 'Boxing Day * sub day
'2011
BankHolidays(16) = #1/3/2011# 'New Year's Day * sub day
BankHolidays(17) = #4/22/2011# 'Good Friday
BankHolidays(18) = #4/25/2011# 'Easter Monday
BankHolidays(19) = #5/2/2011# 'May Bank Holiday
BankHolidays(20) = #5/30/2011# 'Spring Bank Holiday
BankHolidays(21) = #8/29/2011# 'Summer Bank Holiday
BankHolidays(22) = #12/26/2011# 'Christmas Day * sub day
BankHolidays(23) = #12/27/2011# 'Boxing Day * sub day
'2012
BankHolidays(24) = #1/2/2012# 'New Year's Day * sub day
BankHolidays(25) = #4/6/2012# 'Good Friday
BankHolidays(26) = #4/9/2012# 'Easter Monday
BankHolidays(27) = #5/7/2012# 'May Bank Holiday
BankHolidays(28) = #6/4/2012# 'Spring Bank Holiday * sub day
BankHolidays(29) = #6/5/2012# 'Queens Diamond Jubilee Bank Holiday
BankHolidays(30) = #8/27/2012# 'Summer Bank Holiday
BankHolidays(31) = #12/25/2012# 'Christmas Day
BankHolidays(32) = #12/26/2012# 'Boxing Day

Return BankHolidays
End Function

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s