Documentation/Calc Functions/NETWORKDAYS

From The Document Foundation Wiki
Jump to: navigation, search

Function name:

NETWORKDAYS

Category:

Date and Time

Summary:

Returns the number of workdays between a start date and an end date. Holidays can be deducted.

Syntax:

NETWORKDAYS(StartDate; EndDate[; [Holidays][; Workdays]])

Returns:

Returns an integer value which is the number of workdays between the StartDate and the EndDate. It can be negative in scenario where EndDate is earlier than StartDate.

Arguments:

StartDate is the date or a date sequence number or a reference to the cell containing that date from when the calculation is carried out. If the start date is a workday, the day is included in the calculation.

EndDate is the date or a date sequence number or a reference to the cell containing that date up until when the calculation is carried out. If the end date is a workday, the day is included in the calculation.

Holidays is an array of dates or a reference to a range of cells containing them which is a list of holidays. These are non-working days. Enter a cell range in which the holidays are listed individually. Note that this parameter can be omitted as an empty parameter (two consecutive ;; semicolons) to be able to pass the set of Workdays without Holidays.

Workdays is an optional list of numerical values used to specify a different definition for the standard workweek by passing in a list of numbers which define which days of the week are workdays (indicated by 0) or not (indicated by non-zero) in order Sunday, Monday,..., Saturday. So, the default definition of the workweek excludes Saturday and Sunday and is: {1;0;0;0;0;0;1}. To define the workweek as excluding Friday and Saturday, the third parameter would be: {0;0;0;0;0;1;1}.

  • If Workdays is not a valid array then the function returns an error value.

Additional details:

  • Calc's implementation of NETWORKDAYS function is in accordance with ODF 1.2 however Excel doesn't support the optional Workdays parameter.
Note pin.svg

The functions whose names end with _ADD or _EXCEL2003 return the same results as the corresponding Microsoft Excel 2003 functions without the suffix. Use the functions without suffix to get results based on international standards.


Note pin.svg

When entering dates as part of formulas, slashes or dashes used as date separators are interpreted as arithmetic operators. Therefore, dates entered in this format are not recognized as dates and result in erroneous calculations. To keep dates from being interpreted as parts of formulas use the DATE function, for example, DATE(1954;7;20), or place the date in quotation marks and use the ISO 8601 notation, for example, "1954-07-20". Avoid using locale dependent date formats such as "07/20/54", the calculation may produce errors if the document is loaded under different locale settings.


Im-jabber.svg

Note:
Unambiguous conversion is possible for ISO 8601 dates and times in their extended formats with separators. If a #VALUE! error occurs, then unselect Generate #VALUE! error in Tools > Options > LibreOffice Calc > Formula, button Details... in section "Detailed Calculation Settings", Conversion from text to number list box.

Examples:

Formula Description Returns
=NETWORKDAYS("2001-12-15";"2002-01-15";{"2001-12-24"; "2001-12-25"; "2001-12-26"; "2001-12-31"; "2002-01-01"}) The function calculates how many workdays fall between 2001-12-15 and 2002-01-15, considering the following Christmas and New Year holidays: "2001-12-24", "2001-12-25", "2001-12-26", "2001-12-31", "2002-01-01". 17
=NETWORKDAYS(DATE(2016;9;12); DATE(2016;9;25); ; {1;0;0;0;1;1;1}) The function calculats how many workdays fall between September 12nd and 25th in 2016 if only Mondays, Tuesdays, and Wednesdays are considered as workdays. 6

Related LibreOffice functions:

NETWORKDAYS.INTL

WORKDAY

WORKDAY.INTL

ODF standard:

Section 6.10.14, part 2

Equivalent Excel functions:

NETWORKDAYS