Calculate business days between two dates using Domino directory

This tip primarily uses LotusScript to calculate business days between two dates using your Domino directory.

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!

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

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

This was first published in November 2004

Dig deeper on LotusScript

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchWinIT

Search400

  • iSeries tutorials

    Search400.com's tutorials provide in-depth information on the iSeries. Our iSeries tutorials address areas you need to know about...

  • V6R1 upgrade planning checklist

    When upgrading to V6R1, make sure your software will be supported, your programs will function and the correct PTFs have been ...

  • Connecting multiple iSeries systems through DDM

    Working with databases over multiple iSeries systems can be simple when remotely connecting logical partitions with distributed ...

SearchEnterpriseLinux

SearchVirtualDataCentre.co.UK

Close