@BusinessDays (Formula Language)

Returns the number of business days in one or more date ranges.

Syntax

@BusinessDays( startDates ; endDates ; daysToExclude ; datesToExclude )

Note: This @function is new with Release 6.

Parameters

startDates

Time-date or time-date list. The start of each date range.

endDates

Time-date or time-date list. The end of each date range.

daysToExclude

Numer or number list. Optional. Days of the week not counted as business days, where 1 is Sunday and 7 is Saturday. Decimal numbers are rounded to integers. Numbers other than 1-7 are ignored.

datesToExclude

Time-date or time-date list. Optional. Dates not counted as business days.

Return value

numberOfDays

Number or number list. The number of days from startDates to endDates, inclusive, less daysToExclude and datesToExclude that fall within the date range.

Usage

The operation on startDates and endDates is a pair-wise list operation. If they are not the same length, the shorter list is filled out with the value of the last element.

@BusinessDays returns -1 if the calculation produces a negative number of days, an end date precedes a start date, or a time-date value contains only a time.

Examples

  1. This agent displays the number of days in 2001 excluding Saturdays, Sundays, and 10 holidays.
    @Prompt([OK];
@Text(
@BusinessDays([01/01/2001]; [12/31/2001]; 1 : 7;
[01/01/2001] : [01/15/2001] : [02/16/2001] : [05/28/2001] : [07/04/2001] :
[09/03/2001] : [10/08/2001] : [11/22/2001] : [11/23/2001] : [12/25/2001])
);
"Business days in 2001")
  2. This agent displays the number of days in each quarter of 2001 excluding Saturdays, Sundays, and 10 holidays.
    @Prompt([OK];
@Implode(@Text(
@BusinessDays([01/01/2001] : [04/01/2001] : [07/01/2001] : [10/01/2001];
[03/31/2001] : [06/30/2001] : [09/30/2001] : [12/31/2001];
1 : 7;
[01/01/2001] : [01/15/2001] : [02/16/2001] : [05/28/2001] : [07/04/2001] :
[09/03/2001] : [10/08/2001] : [11/22/2001] : [11/23/2001] : [12/25/2001])
); "-");
"Business days in 2001 by quarter")
  3. This field value formula returns the number of days from StartDate to EndDate, inclusive, less NonWorkDays and Holidays. StartDate and EndDate are time-date fields with scalar values. NonWorkDays is a keyword field with alias values of "1" and "7" for Sunday and Saturday. Holidays is a time-date field that allows multiple values.
    @BusinessDays(StartDate; EndDate;
@TextToNumber(NonWorkDays);
Holidays)
  4. This code, when added to a view action in a calendar view that contains a multiple-day event, displays a dialog box that shows the number of business days in the event. For instance, if, in your calendar view, you include a Vacation event that lasts for 32 days (startDT field is 08/02/2002 and endDT field is 09/02/2002), when a user selects the Vacation event from the calendar and clicks on the button, a dialog box appears entitled "Business days" that displays 22.
    @Prompt([OK];"Business days";@Text(@BusinessDays(startDT;endDT;1:7)))

    To account for a holiday on September 2, edit the formula as follows:

    @Prompt([OK];"Business days";@Text(@BusinessDays(startDT;endDT;1:7;[09/02/2002])))