After giving my @BusinessDays observations, a colleague asked about computing business hours. It provided an interesting challenge, but I came up with a LotusScript function to compute the number of business hours between two date/time objects.

The first thing I'll point out before getting started is that this code has only been tested with US date formats. The code doesn't specify month/day/year formatting (the US format), so it should work internationally, but it has not been tested with international settings.

The first thing I needed to do was define the parameters to the function. Obviously, a starting date/time and an ending date/time are needed. The third parameter is an array of integer values for the weekday numbers to be excluded from the computation, just like @BusinessDays. Normally, this will be an array with two integers - 1 and 7. The fourth parameter is an array of NotesDateTime objects that is the list of holidays to exclude, again similar to the @BusinessDays function. Since parameters are required in LotusScript, if you don't want to provide any holidays, you still have to provide an array, but the first element in the array can be the keyword Nothing. The fifth and final parameter is an array of two integers that correspond to the starting time of a business day and the ending time of a business day (in military time). For example, if the business day goes from 9:00 AM to 5:00 PM, then the two parameters will be 900 and 1700.

Function BusinessHours(startTime As NotesDateTime, endTime As NotesDateTime, _
daysToExclude() As Integer, datesToExclude() As NotesDateTime, businessHrs() As Integer) As Double

The value returned is a Double value, although a Long would have provided enough precision for this function (2,147,483,647 is the maximum number for a Long value, which is over 200,000 years assuming every single hour of every single day is a "business hour"). But there could potentially be decimal values returned (a "business day" could be 7.5 hours, for example) so I needed to return something that could return decimals.

The first part of the function defines the variables that will be needed:

   Dim evalStmt As String
   Dim excludePart As String
   Dim i As Integer
   Dim eval As Variant
   Dim numDays As Double
   Dim date1 As NotesDateTime
   Dim date2 As NotesDateTime
   Dim seconds As Double
   Dim hrsIn1BizDay As Double
   Dim numHrs As Double
   Dim temp As Double
   Dim skipStartDay As Integer
   Dim skipEndDay As Integer

The code checks to see if the "business hours" parameter is set up so the starting hour is less than the ending hour. You cannot use this function to compute the total number of hours (there are much easier ways of doing that). And although I don't check to see if the values are between 0 (midnight) and 2359 (12:59 PM), that might be a good addition to this function.

   If businessHrs(0) >= businessHrs(1) Then
      BusinessHours = -1
      Exit Function
   End If

I also want to check to see if the time piece of either the start date/time or the end date/time is "out of bounds". For example, if the work day is set up as 9:00 AM to 5:00 PM, and the start date/time is January 1st at 5:00 AM, then I don't want to inadvertently count the hours from 5:00 AM to 9:00 AM (when business starts that day). So the next block of code puts the start and end times "in bounds".

   Set date1 = New NotesDateTime(startTime.DateOnly & " " & Format$(businessHrs(0), "#0:00") & ":00")
   Set date2 = New NotesDateTime(startTime.DateOnly & " " & Format$(businessHrs(1), "#0:00") & ":00")
   If startTime.TimeDifference(date1) < 0 Then startTime.LSLocalTime = date1.LSLocalTime
   If date2.TimeDifference(startTime) < 0 Then startTime.LSLocalTime = date2.LSLocalTime
   Set date1 = New NotesDateTime(endTime.DateOnly & " " & Format$(businessHrs(0), "#0:00") & ":00")
   Set date2 = New NotesDateTime(endTime.DateOnly & " " & Format$(businessHrs(1), "#0:00") & ":00")
   If endTime.TimeDifference(date1) < 0 Then endTime.LSLocalTime = date1.LSLocalTime
   If date2.TimeDifference(endTime) < 0 Then endTime.LSLocalTime = date2.LSLocalTime

Note how I create temporary date/time objects. I use the DateOnly property and append a time to the value. That's why I say that this should work internationally, because that property should be correct to the local settings and the new date/time object should correctly initialize. But if things don't work and you have some suggestions for me, be sure to post a comment.

Notice how I'm adding in the time value. With the assumption that the time is in military format (a three digit number between 100 and 2359, unless the time is prior to 1:00 AM, which it could be a number less than or equal to 59), that time is split up into either 3 or 4 digits - the first one or two being the hour part and the last two being the minutes part. The seconds are also added in at the end. I'm building a string that ends up looking like a military time value, and that is used when creating the NotesDateTime object.

If the time is out of bounds, the startTime or endTime variable is reset to the edge of the boundary - either the start of the business day or the end of the business day, whichever is appropriate.

Next, the code uses @BusinessDays to compute the number of business days between the two dates. I'm going to use that function multiple times, and the "days to exclude" and "dates to exclude" parameters (3rd and 4th parameters) will not change between uses (the start and end dates will), so those values are built in a separate string so I can quickly refer to the string instead of rebuilding the entire string.

   evalStmt = {@BusinessDays([} & startTime.DateOnly & {]; [} & endTime.DateOnly & {]; }
   excludePart = ""

So, the statement to evaluate starts out with the start date and end date in bracket notation. The variable excludePart will contain the string for the 3rd and 4th parameter, and will be built using the arrays passed into this function.

   For i = 0 To Ubound(daysToExclude)
      excludePart = excludePart & Cstr(daysToExclude(i))
      If i <> Ubound(daysToExclude) Then excludePart = excludePart & " : "
   Next

This block looks at the weekday numbers to exclude and adds those to the string variable, with a colon between each entry. Note that I cannot use a built-in function like Join here because the data type of each element is an integer. If the array itself was a Variant, then it would have been possible.

   If Not datesToExclude(0) Is Nothing Then
      excludePart = excludePart & {; }
      For i = 0 To Ubound(datesToExclude)
         excludePart = excludePart & "[" & datesToExclude(i).DateOnly & "]"
         If i <> Ubound(datesToExclude) Then excludePart = excludePart & " : "
      Next
   End If

The next block (shown above) adds in the holidays. If the first element of the array is Nothing then the 4th parameter to @BusinessDays will be skipped. Otherwise, the dates are put in using bracket notation with a colon between each entry.

   evalStmt = evalStmt & excludePart & {)}
   eval = Evaluate(evalStmt)
   numDays = Cdbl(eval(0))
   If numDays <= 0 Then
      BusinessHours = numDays
      Exit Function
   End If

The two pieces of the statement are joined together and the statement is evaluated. If you don't know how @BusinessDays works, you can refer to the Domino Designer help documentation or refer to this document for my observations.

If @BusinessDays returns a negative number, then the start date is later than the end date. If it returns 0, then the there are no business days between the start and end date. This would happen if the dates were the same date and it was a holiday or a weekend date. There are other scenarios (a holiday before or after a weekend, for example). So, if zero or a negative number is returned from the @BusinessDays function, that value will be returned from this function. So it will behave in a similar fashion - a start date that is later than an end date will return -1 no matter how far apart the days are.

   If numDays = 1 Then
      seconds = endTime.TimeDifferenceDouble(startTime)
      If seconds < 0 Then
         BusinessHours = -1
      Else
         BusinessHours = (seconds / 60) / 60
      End If
      Exit Function
   End If

If @BusinessDays returns the value 1, then the start day and end day are the same working day. In this case, we simply want to compute the number of hours between the two times. (Remember, we adjusted for "out of bounds" earlier, so that doesn't have to be accounted for here). Compute the number of seconds difference between the two times. If it's negative, then the end time is before the start time and -1 is returned. Otherwise, the seconds are converted into minutes and hours, and that value is returned.

The next thing I want to do is know if the first day and/or the last day were included in the @BusinessDays calculation. This is important. For example, if the first day is on a Sunday, then I don't need to look at those hours and can instead start with Monday's hours. But if the first day is a Monday at 10:00 AM, I need to include the business hours from 10:00 AM to the end of that business day.

In order to do these checks, I run through @BusinessDays again, but this time with only the start date (first time) and the end date (second time). Both times I'll get back a value of 0 if the date was not included, or a 1 if the date was included. I set a variable to either True or False to let me know whether the date was skipped or not.

   skipStartDay = False
   evalStmt = {@BusinessDays([} & startTime.DateOnly & {]; [} & startTime.DateOnly & {]; }
   evalStmt = evalStmt & excludePart & {)}
   eval = Evaluate(evalStmt)
   If eval(0) = 0 Then skipStartDay = True

The end day follows the same logic:

   skipEndDay = False
   evalStmt = {@BusinessDays([} & endTime.DateOnly & {]; [} & endTime.DateOnly & {]; }
   evalStmt = evalStmt & excludePart & {)}
   eval = Evaluate(evalStmt)
   If eval(0) = 0 Then skipEndDay = True

The included days between the start day and the end day will always be full working days, so we need to know how many hours are in a working day. The next block of code does that:

   Set date1 = New NotesDateTime("01/01/1980 " & Format$(businessHrs(0), "#0:00") & ":00")
   Set date2 = New NotesDateTime("01/01/1980 " & Format$(businessHrs(1), "#0:00") & ":00")
   hrsIn1BizDay = date2.TimeDifference(date1)
   hrsIn1BizDay = (hrsIn1BizDay / 60) / 60

Now, we can finally start computing the number of business hours between the two dates. First, start by computing the number of hours between the two dates, based on the original @BusinessDays function. We don't want to include the first day and the last day (those will come next), so we have to exclude those days if they were included in the original formula. However, if either one (or both) were excluded in the original formula (because the start or end date was on a weekend or holiday, for example), then we don't want to exclude them twice. So an If block is used to know how many days to exclude from the original formula.

   If skipStartDay = True And skipEndDay = True Then
      numHrs = hrsIn1BizDay * numDays
   Elseif skipStartDay = True And skipEndDay = False Then
      numHrs = hrsIn1BizDay * (numDays-1)
   Elseif skipStartDay = False And skipEndDay = True Then
      numHrs = hrsIn1BizDay * (numDays-1)
   Else
      numHrs = hrsIn1BizDay * (numDays-2)
   End If

Finally, we add on the number of hours in the start day and the number of hours in the end day. If either one was excluded in the original formula, then there's nothing extra to add. But if it was included in the original formula, then it wasn't accounted for in the block of code just completed and needs to be added in:

   If Not skipStartDay Then
      Set date1 = New NotesDateTime(startTime.DateOnly & " " & Format$(businessHrs(1), "#0:00") & ":00")
      temp = date1.TimeDifference(startTime)
      temp = (temp / 60) / 60
      numHrs = numHrs + temp
   End If

I compute the number of hours from the starting date/time to the end of business that day. (Remember that I checked the bounds earlier, so I know the number of seconds will be zero or greater). Those seconds are converted into minutes and then hours and added to the running total.

   If Not skipEndDay Then
      Set date1 = New NotesDateTime(endTime.DateOnly & " " & Format$(businessHrs(0), "#0:00") & ":00")
      temp = endTime.TimeDifference(date1)
      temp = (temp / 60) / 60
      numHrs = numHrs + temp
   End If

The same logic is applied to figure out the number of hours between the start of business on the end day and the ending time. Those number of hours are added to the running total.

   BusinessHours = numHrs
End Function

The running total is returned as the result of the function.