Adjust Date/Time To Next Business Day

June 10, 2007 – 10:41 am

Finding the difference in seconds between one date-time and another can be done using the timedifference method of the NotesDateTime class in LotusScript. In formula language, @BusinessDays returns the number of business days in one or more date ranges.
Assume you have the following requirements:

Calculate the time in minutes between “DateCreated” and “DateClosed”.
Businessdays are from Monday to Friday
BusinessHours ar from 8 AM to 10 PM
Exclude holidays
If the date/time value is greater than the end of a businessday, set date to next businessday
if the date/time value is before the beginning of a businessday, set the time to i.e 8 AM

Here is a LotusScript class which does the trick.

Class DateTimeCalculator
 
	Private StartDT As NotesDateTime
	Private EndDT As NotesDateTime
	Private dt3 As NotesDateTime
	Private dt4 As NotesDateTime
	Private dt5 As NotesDateTime
	Private NewDT As Boolean
	Private tmp As Variant
	Private i As Integer
	Private j As Integer
	Private k As Integer
	Private x As Integer
	Private elapsed As Integer
	Private ExcludeDays() As String
	Private ExcludeDates() As NotesDateTime
	Private WDENDHOUR As String
	Private WDSTARTHOUR As String
 
	Sub New (strExcludeDays As String_
                       , strExcludeDates As String_
                       , strWDSTARTHOUR As String_
                       , strWDENDHOUR As String)
 
		If strWDSTARTHOUR = "" Then
			WDSTARTHOUR = "00:00"
		Else
			WDSTARTHOUR = strWDSTARTHOUR
		End If
 
		If strWDENDHOUR = "" Then
			WDENDHOUR = "23:59"
		Else
			WDENDHOUR = strWDENDHOUR
		End If
 
		' strExcludeDays contains a comma separated list of dayes that are not work days
		tmp = Split(strExcludeDays,",")
		Redim Me.ExcludeDays(Ubound(tmp))
		For x = 0 To Ubound(tmp)
			Me.ExcludeDays(x) = tmp(x)
		Next
 
		' strExcludeDates contains a comma separated list of dates that are not work days
		tmp = Split(strExcludeDates,",")
		Redim Me.ExcludeDates(Ubound(tmp))
		For x = 0 To Ubound(tmp)
			Set Me.ExcludeDates(x) = New NotesDateTime(tmp(x))
		Next
	End Sub
 
	Public Function GetNextBusinessDay ( dt1 As String )As String
		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 StartDT.AdjustDay(1)
		End If
 
		For j = 0 To Ubound ( Me.ExcludeDates ) ' Check for excluded dates
			For k = 0 To Ubound ( Me.ExcludeDates )
				If Me.StartDT.DateOnly = Me.ExcludeDates(k).DateOnly Then
					Call Me.StartDT.AdjustDay(1)
					NewDT = True
					For i = 0 To Ubound ( Me.ExcludeDays ) ' Check if businessday
						If Instr(Implode ( Me.ExcludeDays )_
                                                    , Cstr(Weekday(Me.StartDT.DateOnly))) > 0 Then
							Call Me.StartDT.AdjustDay(1)
						End If
					Next
					k = Ubound(Me.ExcludeDates)
				Else
					For i = 0 To Ubound ( Me.ExcludeDays ) ' Check if businessday
						If Instr(Implode ( Me.ExcludeDays _
                                                    ), Cstr(Weekday(Me.StartDT.DateOnly))) > 0 Then
							Call Me.StartDT.AdjustDay(1)
							NewDT = True
						End If
					Next
				End If
			Next
		Next
 
		If NewDT Then
			Set dt5 = _
                          New NotesDateTime (Me.StartDT.DateOnly & " " & Me.WDSTARTHOUR)
			Set Me.StartDT = dt5
		End If
		GetNextBusinessDay = Me.StartDT.LocalTime
	End Function
 
End Class

This class has a New function that is used to instantiate the class. A list of business days, list of holidays and the start and end of the workday is passed when the object is created.

1	Dim DTCalc As New DateTimeCalculator ( "1,7", "11.06.2007","08:00", "22:00")

Once instatiated you can now adjust a date/time value to the next business by calling the GetNextBusinessDay method and passing the date/time value as a string parameter.

1
2
3

	StartDT = doc.GetFirstItem("DateCreated").text
         ...
        Set dt1 = New NotesDateTime ( DTCalc.GetNextBusinessDay(StartDT) )

The returned value can now be used to find the difference between this date/time value and another one.

1	diff = dt2.TimeDifference(dt1)/60

Here is an example of how to put this all together in a click button. I have attached a sample database at the end of this article.

Sub Click(Source As Button)
	Dim s As New NotesSession
	Dim db As NotesDatabase
	Dim col As NotesDocumentCollection
	Dim doc As NotesDocument
	Set db = s.CurrentDatabase
	Set col = db.UnprocessedDocuments
	Set doc = col.GetFirstDocument
 
	Dim msg As String
	Dim StartDT As String
	Dim EndDT As String
	Dim dt1 As NotesDateTime
	Dim dt2 As NotesDateTime
	Dim diff As Long
 
	StartDT = doc.GetFirstItem("DateCreated").text
	EndDT = doc.GetFirstItem("DateClosed").text
 
	Dim DTCalc As New DateTimeCalculator ( "1,7", "11.06.2007","08:00", "22:00")
 
	Set dt1 = New NotesDateTime ( DTCalc.GetNextBusinessDay(StartDT) )
	Set dt2 = New NotesDateTime ( DTCalc.GetNextBusinessDay(EndDT) )
 
	diff = dt2.TimeDifference(dt1)/60
	msg = msg  & "Created On : " & StartDT & CRLF
	msg = msg  & "Closed On : " & EndDT & CRLF
	msg = msg & "Difference : " & diff & " minutes"
	Msgbox msg
End Sub

Download SampleDB

Posted in Source Code |

5 Responses to “Adjust Date/Time To Next Business Day”
I tackled this same sort of thing a couple of years ago. The client wanted to know how long a certain trouble ticket had been open, both real time and business hours. It caused me to do exactly the same thing, namely create my own LS class.

I posted my class on OpenNTF. You might want to take a look at it and see how similar our approaches were.

Sean—

By Sean Burgess on Jun 15, 2007
Sean,

I have tried your class and found that it does not seem to calculate the elapsed time correctly. When I use this code for example:

Dim elTime As New ElapsedTime( “13.06.2007 10:00″, “14.06.2007 10:20″, “” )
Msgbox elTime.GetBElapsedString()

leaving the default settings for nondays and business hours untouched, the result is “20 Minutes” which is … not correct.
When I wrote my code, I did not have the time to dig too deep into your code to search for the reason, why I do not get the correct result. So I decided to write it from scratch.

Ulrich

By eknori on Jun 15, 2007
I have used the code in production for a while, so I will have to investigate what the issue is. Maybe I screwed something up when I was trying to comment the code.

By Sean Burgess on Jun 15, 2007
Found the problem in my code and updated OpenNTF. It’s always the little things that get you, like checking for False when you should be checking for True.

By Sean Burgess on Jun 18, 2007
Thanks for investigating and updating the code …

By eknori on Jun 19, 2007

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

eknori.de