VIEW MEMBER FEEDACK TO THIS TIP
This tip primarily uses Lotus Script to calculate business days between two dates using your Domino directory. I've never used the code on the Web. This code not only shows you how to calculate business days between two dates, it also shows you many advanced coding techniques that you can learn from and use elsewhere. As this code will take about an hour to implement, you may forget to vote on my tip -- please try not to!
- To begin, create a new form.
- Create two date fields reqStartDate and reqEndDate. Make these two fields editable and of type date/time. In the exiting event of both of these fields place the following code:
'***Start of exit event code**************
******* Dim w As New NotesUIWorkspace
Dim uidoc As NotesUIDocument
Set uidoc=w.currentDocument
Dim doc As NotesDocument
Set doc=uidoc.document
doc.reqTotalDays=calcTotalDays
Call uidoc.refresh
'**End of exit event code*********************
- On the same form create another field called reqTotalDays. Make this field computed and of type number. This field will hold the total business days between the two dates reqStartDate and reqEndDate.
- Create a hidden field called "myHolgroups." Give this field a default value(s) of whatever your holiday group(s) are going to be.
'Open your Domino directory
(names.nsf). Navigate to the following location:
'For R6: Configuration/Miscellaneous/Holidays.
'For R5: Server/Holidays.
You get the idea, we are looking for the view called holidays. In this view you will notice a load of holidays categorized by group. In my view these groups are generally Countries. We don't use these, so we created a new group for our company called "FF." I then always default the fields "myHolGroups" to "FF." Then when my functions look up this view they only loop through the documents with a group "FF."
- In the Globals section of you form paste the following five functions:
'FUNCTION ONE******************
Function calcTotalDays As Variant
Dim w As New NotesUIWorkspace
Dim uidoc As NotesUIDocument
Set uidoc=w.currentDocument
Dim doc As NotesDocument
Set doc=uidoc.document
Dim x As Integer, y As Integer
x=calcWeekDays(doc.reqStartDate(0),
doc.reqEndDate(0))
y=calcWeekDayHolidays(doc,
doc.reqStartDate(0), doc.reqEndDate(0))
'CheckForXmasEve
z = checkForXmasEve(doc.reqStartDate(0),
doc.reqEndDate(0))
'NOTE TO INCLUDE CHRISTMAS EVE AS
A BUSINESS DAY CHANGE THE (-) MINUS Z
TO (+) PLUS Z.
calcTotalDays= ((x - y) - z)
Call uidoc.Refresh
End Function
'**END OF FUNCTION ONE**************
'***FUNCTION TWO******************
Function calcWeekDays(first As Variant,
last As Variant) As Integer
Dim count As Integer 'number of days in
range that are not sat/sun or
holiday.
Dim current As Variant
current = first
Do Until current>last
'check to see if day is a Sat or a sun
If Weekday(current)>1
And Weekday(current)<7 Then
count=count+1
End If
current=current+1
Loop
calcWeekdays=count
End Function
'***END OF FUNCTION TWO*****************
'***FUNCTION THREE******************
Function calcWeekDayHolidays(doc As
NotesDocument, first As Variant, last As
Variant) As Double
'get handle to nab to search for holidays
Dim db As New NotesDatabase(getNabSvr,
"names.nsf") ' THE FILE NAMES OF
YOUR DOMINO DIECTORY WHERE
'YOUR HOLIDAYS ARE STORED
'OUR DOMINO DIRECTORY IS CALLED
NAMES.NSF, YOUR MIGHT NOT BE!!!
Dim x As String, y As String, z As String,
qry As String
'query to search nab for holiday documents
where the holiday
startDateTime is between frist and last dates
x = {Form="Holiday" & RepeatStartDate>=[}
& first & {] &
RepeatStartDate<=[} & last & {] }
'code to read my holiday groups -
stored in NAB Extn dB. Place in
hidden field 'myHolGroups' on this form
'builds a query to add to the search to
make sure that any holidays
found in NAB belong to one of my holiday groups
For i = 0 To Ubound(doc.myHolGroups)
y=y & { | @IsMember("} &
doc.myHolGroups(i) & {"; HolidayGroup)}
Next
y= {(} + Right(y, Len(y)-2) + {)}
qry = x + " & " + y
Dim coll As NotesDocumentCollection
'collection of all holiday docs
applicable to my user.
Set coll=db.search(qry, Nothing, 0)
Dim hDoc As NotesDocument
Dim cnt As Integer, holDate As Variant
cnt=coll.count 'number of holidays.
For i=1 To coll.count 'We've already
discounted weekends so we now have
to appropriately consider weekend hols.
Set hDoc=coll.getNthDocument(i)
holDate=hDoc.RepeatStartDate(0)
If Weekday(holDate)=1
Or Weekday(holDate)=7 Then
If hDoc.repeatWeekends(0)="D"
Then 'if holiday falls on
weekend and is not moved it doesn't count
cnt=cnt-1
Else 'All holidays in Ireland
that move from weekends
roll to nearest Monday
If Weekday(holDate)=1 Then
effectiveStartDate=hDoc.RepeatStartDate
(0)+1 'Sunday moves to Monday
Else
effectiveStartDate=hDoc.RepeatStartDate
(0)+2 'Sat moves to Monday
End If
If effectiveStartDate>last Then
'Check for xmas eve.
cnt=cnt-1 'if moving holiday moves it
outside period of employee request doesn't count.
End If
End If
End If
Next
calcWeekDayHolidays=cnt
End Function
'***END OF FUNCTION THREE*****************
'***START OF FUNCTION FOUR*****************
'NOTE: THIS FUNCTION CHECKS FOR
CHRISTMAS EVE AND GIVES A HALF DAYS
'ALWAYS FOR XMAS EVE AS MY
COMPANY ALWAYS
'GIVE A HALF DAY ON XMAS EVE.
'I HAVE EXPAINED ABOVE HOW TO
DEAL WITH XMAS EVE
Function checkForXmasEve
(first As Variant, last As Variant) As Variant
Dim count As Variant
Dim current As Variant
current = first
Do Until current>last
'Check for Xmas eve - assign .5 day if found
sdate = current
sday = Day(sdate)
If sday = "24" Then
sMonth = Month(sdate)
If smonth = "12" Then
count = count + .5
End If
End If
current=current+1
Loop
checkForXmasEve=count
End Function
'***END OF FUNCTION FOUR*****************
'***START OF FUNCTION FIVE
Function getNabSvr As String
'Returns a string which is the name of
the server on which to access
the Domino Directory.
'If the database is local, it returns a
known server. Otherwise it
returns the same server that
'the CURRENT DATABASE is running on.
Dim s As New NotesSession
Dim db As NotesDatabase
Set db=s.currentdatabase
If db.server="" Then
getNabSvr="THE NAME OF YOUR SERVER"
Else
getNabSvr=db.server
End If
End Function
'***END OF FUNCTION FIVE****************
If you want to, you can also do the following:
In the input validation event of the field reqEndDate enter the following formula:
@If(reqEndDate<ReqStartDate;
@Failure("End date must be after Start
date."); @Success)
For both date fields default them to @Today.
If you need help please e-mail me at gary.cronin@friendsfirst.ie.
MEMBER FEEDBACK TO THIS TIP
With R6 introducing the @BusinessDays function it may be more stable or easier to implement this code using the evaluate statement. An example is given below.
Function GetBusinessDays
( date1 As String, date2 As String )
As Long
On Error Goto errhandle
Dim returnArray As Variant
Dim eval$
If ( Isdate( date1) = False ) Then
Error 1, "Date1 is an invalid date."
End If
If ( Isdate( date2) = False ) Then
Error 1, "Date2 is an invalid date."
End If
eval$ = |@BusinessDays
( @textToTime("| & date1 & |");
@TextToTime("| & date2 & |") )|
Print eval$
returnArray = Evaluate( eval$)
If Isarray( returnArray ) Then
If Cstr( returnArray(0) ) <> "" Then
If Isnumeric( returnArray(0) ) Then
GetBusinessdays =
Clng( returnArray(0))
Else
Error 1,
"Error evaluating business days.
Error =" & Join( returnArray)
End If 'Isnumeric( returnArray(0) )
Then
Else
Error 1, "Error evaluating
business days.
No data returned."
End If ''Cstr( returnArray(0) ) <> ""
Else 'not an array
Error 1, "Unable to evaluate
the business days
with the dates given."
End If 'Isarray( returnArray )
Exit Function
errhandle :
Error Err, "[GetBusinessDays]
(" & Cstr( Erl) & ")"
& Chr$(13) & Error$
End Function
—Shane H.
Do you have comments on this tip? Let us know.
This tip was submitted to the SearchDomino.com tip exchange by member Gary Cronin. Please let others know how useful it is via the rating scale below. Do you have a useful Notes/Domino tip or code to share? Submit it to our monthly tip contest and you could win a prize and a spot in our Hall of Fame.
