Calculate Elapsed Time Between Two Date/Time Values

June 17, 2007 – 5:26 pm

For my OpenNTF project !!HELP!! I needed a function to calculate the elapsed time between two events. The code should be able to exclude holidays and weekends. In addition it should calculate the time difference only within workhours. Here is the result of a rainy sunday :-)
There is another class by Sean Burgess, which does the same stuff than mine.

The following sample returns the amount of time in minutes between two given date/time values

1
2
3
4
5
6
7
8
9
10

Sub Click(Source As Button)
	Dim startdt As String
	Dim enddt As String
	Dim dtc As New DateTimeCalculator ( "1,7","24.12.2007","7:00~17:00")
 
	startdt = "18.06.2007 16:59"
	enddt = "19.06.2007 07:01"
 
	Msgbox dtc.GetElapsedTime(startdt,enddt)
End Sub

Copy the following code to a script library. Type

1

Use "YourLibName"

into the Options section of your button, agent or whereever you like to use the lib. Don’t forget to include Julian Robichaux’s OpenLog for error trapping.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151

Class DateTimeCalculator
 
	Private StartDT As NotesDateTime
	Private EndDT As NotesDateTime
	Private dt3 As NotesDateTime
	Private dt4 As NotesDateTime
	Private dt5 As NotesDateTime
	Private WDENDHOUR As String
	Private WDSTARTHOUR As String
	Private nondays As String
	Private holidays As String
 
	Sub New (strExcludeDays As String,strExcludeDates As String,SERVICEHOURS As String)
		On Error Goto ERRHANDLE
 
		WDSTARTHOUR = "00:00"
		WDENDHOUR = "23:59"
		nondays = "0"
		holidays = "[01/01/1899]"
 
		If SERVICEHOURS <> "" Then
			WDSTARTHOUR = Strtoken(SERVICEHOURS,"~",1)
			WDENDHOUR = Strtoken(SERVICEHOURS,"~",2)
		End If
		If strExcludeDays <> "" Then
			nondays = Implode(Split(strExcludeDays,","),":")
		End If
		If strExcludeDates <> "" Then
			holidays = Implode(Split(strExcludeDates,","),"]:[")
			holidays = "[" & holidays & "]"
		End If
 
EXITPOINT:
		Exit Sub
ERRHANDLE:
		Call LogError()
		Resume EXITPOINT
	End Sub
 
	Public Function GetNextBusinessDay (dt1 As String) As String
		On Error Goto ERRHANDLE
		Dim newDT As Boolean
		Set Me.StartDT = New NotesDateTime(dt1)
		NewDt = False
		Set dt3 = New NotesDateTime(Me.StartDT.DateOnly & " " & Me.WDSTARTHOUR)
		Set dt4 = New NotesDateTime(Me.StartDT.DateOnly & " " & Me.WDENDHOUR)
		If Me.StartDT.TimeDifference(dt3) < 0 Then ' StartDT < WDSTARTHOUR
			Set Me.StartDT = dt3
		End If
 
		If dt4.TimeDifference(Me.StartDT) < 0 Then ' StartDT > WDENDHOUR
			Set Me.StartDT = dt3
			Call Me.StartDT.AdjustDay(1)
		End If
 
		While Me.GetBusinessDays (StartDT.DateOnly,StartDT.DateOnly ) = 0
			Call StartDT.AdjustDay(1)
			NewDT = True
		Wend
 
		If NewDT Then
			Set dt5 = New NotesDateTime(Me.StartDT.DateOnly & " " & Me.WDSTARTHOUR)
			Set Me.StartDT = dt5
		End If
 
		GetNextBusinessDay = Me.StartDT.LocalTime
 
EXITPOINT:
		Exit Function
ERRHANDLE:
		Call LogError()
		Resume EXITPOINT
	End Function
 
	Private Function GetNumBusinessDayHours () As Integer
		On Error Goto ERRHANDLE
		Dim BDHOURS1 As New NotesDateTime(Today & " " & Me.WDSTARTHOUR)
		Dim BDHOURS2 As New NotesDateTime(Today & " " & Me.WDENDHOUR)
		GetNumBusinessDayHours = Fix(((BDHOURS2.TimeDifference(BDHOURS1)/60)Mod 1440)/60)
 
EXITPOINT:
		Exit Function
ERRHANDLE:
		Call LogError()
		Resume EXITPOINT
	End Function
 
	Private Function GetTimeDifference (strStart As String,strEnd As String ) As Long
		On Error Goto ERRHANDLE
		Dim BDSTART As New NotesDateTime(strStart)
		Dim BDEND As New NotesDateTime(strEnd)
		GetTimeDifference = Fix((BDEND.TimeDifference(BDSTART)/60)Mod 1440)
 
EXITPOINT:
		Exit Function
ERRHANDLE:
		Call LogError()
		Resume EXITPOINT
	End Function
 
	Public Function GetBusinessDays(dtStart As String,dtEnd As String) As Integer
		On Error Goto ERRHANDLE
		Dim busdays As Variant
 
		Dim BDS As New NotesDateTime(dtStart)
		Dim BDE As New NotesDateTime(dtEnd)
 
		busdays = Evaluate(_
		{@BusinessDays([}&_
		Cdat(BDS.DateOnly)& {];[}&_
		Cdat(BDE.DateOnly)& {];}&_
		Me.nondays &{;}&_
		Me.holidays & {)})
		GetBusinessDays = Cint(busdays(0))
 
EXITPOINT:
		Exit Function
ERRHANDLE:
		Call LogError()
		Resume EXITPOINT
	End Function
 
	Public Function GetElapsedTime (dtStart As String,dtEnd As String) As Long
		On Error Goto ERRHANDLE
		Dim intStart As Long
		Dim intMiddle As Long
		Dim intEnd As Long
		Dim i As Integer
 
		Set dt3 = New NotesDateTime(dtStart)
		Set dt4 = New NotesDateTime(dtEnd)
		If dt3.DateOnly = dt4.dateonly Then ' same day
			GetElapsedTime = Me.GetTimeDifference(dtStart,dtEnd)
		Else
			intStart = Me.GetTimeDifference(dtStart,Cstr(dt3.DateOnly & " " & Me.WDENDHOUR))
			intMiddle = 0
			i = Me.GetBusinessDays(dtStart,dtEnd)-2
			If i > 0 Then
				intMiddle = (i*Me.GetNumBusinessDayHours())*60
			End If
			intEnd = Me.GetTimeDifference(Cstr(dt4.DateOnly & " " & Me.WDSTARTHOUR),dtEnd)
			GetElapsedTime = intStart+intMiddle+intEnd
		End If
 
EXITPOINT:
		Exit Function
ERRHANDLE:
		Call LogError()
		Resume EXITPOINT
	End Function
End Class

Posted in Source Code |

Related posts:

  1. UNIX timestamp to Date function
  2. Display Date Range As Bar In Notes Views
  3. Adjust Date/Time To Next Business Day – Part II
  4. @BusinessDays In LS
  5. Adjust Date/Time To Next Business Day

Sorry, comments for this entry are closed at this time.

eknori.de is proudly powered by WordPress Entries (RSS) and Comments (RSS). Theme by Bob -->