Calculate business days between two dates using Domino directory

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!

1. To begin, create a new form.

2. 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*********************
   

3. 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.

4. 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

1.  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."

2. 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

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.

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.