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:
- Create a form called .Holiday
- Add a field to the form called HolidayDate
- Create a view called .Holidays to include your .Holiday documents
- 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. DateTimeValue dtReported.SetAnyTime 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 Else Min = b End If End Function Function Max( a As Variant, b As Variant ) As Variant If a > b Then Max = a Else Max = b End If End Function