Returns the number of business days in one or more date ranges.
@BusinessDays( startDates ; endDates ; daysToExclude ; datesToExclude )
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.
numberOfDays
Number or number list. The number of days from startDates to endDates, inclusive, less daysToExclude and datesToExclude that fall within the date range.
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.
@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")
@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")
@BusinessDays(StartDate; EndDate;
@TextToNumber(NonWorkDays);
Holidays)
@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])))