Documentation/Calc Functions/NETWORKDAYS.INTL

From The Document Foundation Wiki
Jump to: navigation, search

Function name:

NETWORKDAYS.INTL

Category:

Date and Time

Summary:

Gives the number of workdays between a start date and an end date. There are options to define weekend days and holidays. The optional weekend parameter (or a string) can be used to define the weekend days (or the non-working days in each week). Also, optionally, the user can define a holiday list. The weekend days and user-defined holidays are not counted as working days.

Syntax:

NETWORKDAYS.INTL(StartDate; EndDate[; [Weekend] [; Holidays] ])

Returns:

Returns an integer value which is the number of workdays between StartDate and EndDate considering Holidays and Weekend (as defined by the parameters) as non-working days.

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.

Weekend is an optional parameter – a number or a string used to specify the days of the week that are weekend days and are not considered working days. Weekend is a weekend number or string that specifies when weekends occur. Weekend number values indicate the following weekend days:

Number 1 to 7 for two-day weekends and 11 to 17 for one-day weekends.

Number Weekend
1 or omitted Saturday and Sunday
2 Sunday and Monday
3 Monday and Tuesday
4 Tuesday and Wednesday
5 Wednesday and Thursday
6 Thursday and Friday
7 Friday and Saturday
11 Sunday only
12 Monday only
13 Tuesday only
14 Wednesday only
15 Thursday only
16 Friday only
17 Saturday only

Weekend string provides another way to define the weekly non-working days. It must have seven (7) characters – zeros (0) for working day and ones (1) for non-working day. Each character represents a day of the week, starting with Monday. Only 1 and 0 are valid. “1111111” is an invalid string and should not be used. For example, the weekend string “0000011” defines Saturday and Sunday as non-working days.

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.

  • If StartDate or EndDate is not within the range for the current date base value, then the function returns a #VALUE! error.
  • If Weekend is anything else except for 1 to 7, 11 to 17, and a valid string containing exact 7 integers either 0 or 1 (but not 1111111), then the function returns a #VALUE! error.

Additional details:

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:

A B
1 StartDate 2016-12-15
2 EndDate 2017-01-14
3 Weekend 13
4 Holidays 2016-12-24
5 2016-12-25
6 2016-12-26
7 2016-12-31
8 2017-01-01
Formula Description Returns
=NETWORKDAYS.INTL(B1;B2;;B4:B8) The function calculates the number of workdays with default for weekend days. 21
=NETWORKDAYS.INTL(B1;B2;11;B4:B8) The function calculates the number of workdays with Sunday only weekends. 24
=NETWORKDAYS.INTL(B1;B2;“0000001”;B4:B8) Alternatively, use the weekend string “0000001” to define Sunday as the non-working day of every week. 24
=NETWORKDAYS.INTL(B1;B20) The function can be used without the two optional parameters – weekday and holidays – by leaving them out. 22

Related LibreOffice functions:

NETWORKDAYS

WORKDAY

WORKDAY.INTL

ODF standard:

None

Equivalent Excel functions:

NETWORKDAYS.INTL