Manage Learn to apply best practices and optimize your operations.

Find number of working days

This tip describes how to calcualte the number of working days.

This LotusScript function finds out how many working days there are between two dates, excluding weekends and holidays. To make it work for you:

  1. Create a form called .Holiday
  2. Add a field to the form called HolidayDate
  3. Create a view called .Holidays to include your .Holiday documents
  4. Insert the LotusScript as detailed below into your agent/whatever.

The number of days returned is fully inclusive of the end days you specify (e.g. 06/01/03 to 07/01/03 would return 2). For semi-inclusive/exclusive deduct 1/2 respectively. Hope this helps you out.

Within your LotusScript, to find out how many working days there are between 2 dates, call the function as follows:

Dim niReportedDate As NotesItem
Dim dtReported As NotesDateTime

Set niReportedDate = docSR.
GetFirstItem( "ComposeDate" )
Set dtReported = niReportedDate.

Dim dtToday as New NotesDateTime
( "Today" )
Print "Working days elapsed = " 
& WorkingDays( dtReported, dtToday )

For the above example to work you would
 need to define db as a NotesDatabase global 
variable assigned to the current database 
and docSR as a NotesDocument variable, 
refering to a document. This document would 
have a ComposeDate item containing 
a date/time value. 

Function WorkingDays(dtFrom As 
notesdatetime, dtTo As notesdatetime ) As Long
 Dim lDays As Long
 Dim viewHoliday As NotesView
 Dim sSearch As String
 Dim lDayFrom As Long
 Dim lDayTo As Long
 Dim lWeekDays As Long
 Dim lHolidays As Long
 lDays = dtTo.TimeDifference(dtFrom) / 86400
 Set viewHoliday = db.GetView( ".Holidays" )
 sSearch = {FIELD HolidayDate >= } 
& dtFrom.DateOnly _
 & { & FIELD HolidayDate <= } & dtTo.DateOnly
 lHolidays = viewHoliday.FTSearch( sSearch, 0 )
 lDayFrom = Weekday(dtFrom.LsLocalTime) Mod 7
 lDayTo = Weekday(dtTo.LsLocalTime) Mod 7
 lWeekDays = (lDays - lDayTo + lDayFrom - 7) * 5 / 7 _
 - Max(-2, -lDayFrom ) - Min(1, lDayTo ) 
+ 5 - lDayFrom + lDayTo
 WorkingDays = lWeekDays - lHolidays
End Function

Function Min( a As Variant, b As Variant ) As Variant
 If a < b Then
  Min = a
  Min = b
 End If
End Function

Function Max( a As Variant, b As Variant ) As Variant
 If a > b Then
  Max = a
  Max = b
 End If
End Function

Dig Deeper on LotusScript



  • Favorite iSeries cheat sheets

    Here you'll find a collection of valuable cheat sheets gathered from across the iSeries/ community. These cheat ...

  • HTML cheat sheet

    This is a really cool cheat sheet if you're looking to learn more about HTML. You'll find just about everything you every wanted ...

  • Carol Woodbury: Security

    Carol Woodbury