NETWORKDAYS.INTL

Returns 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.

tip

This function is available since LibreOffice 4.3


Syntax

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

StartDate is the 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 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 optional list of dates that must be counted as non-working days. The list can be given in a cell range.

note

ΠŸΡ€ΠΈ Π²Π²ΠΎΠ΄Π΅ Π΄Π°Ρ‚ ΠΊΠ°ΠΊ части Ρ„ΠΎΡ€ΠΌΡƒΠ», косыС Ρ‡Π΅Ρ€Ρ‚Ρ‹ ΠΈΠ»ΠΈ Ρ‚ΠΈΡ€Π΅, ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΡƒΠ΅ΠΌΡ‹Π΅ Π² качСствС Ρ€Π°Π·Π΄Π΅Π»ΠΈΡ‚Π΅Π»Π΅ΠΉ Π΄Π°Ρ‚, ΠΈΠ½Ρ‚Π΅Ρ€ΠΏΡ€Π΅Ρ‚ΠΈΡ€ΡƒΡŽΡ‚ΡΡ ΠΊΠ°ΠΊ арифмСтичСскиС ΠΎΠΏΠ΅Ρ€Π°Ρ‚ΠΎΡ€Ρ‹. ΠŸΠΎΡΡ‚ΠΎΠΌΡƒ Π΄Π°Ρ‚Ρ‹, Π²Π²Π΅Π΄Π΅Π½Π½Ρ‹Π΅ Π² этом Ρ„ΠΎΡ€ΠΌΠ°Ρ‚Π΅, Π½Π΅ Ρ€Π°ΡΠΏΠΎΠ·Π½Π°ΡŽΡ‚ΡΡ ΠΊΠ°ΠΊ Π΄Π°Ρ‚Ρ‹ ΠΈ приводят ΠΊ ΠΎΡˆΠΈΠ±ΠΎΡ‡Π½Ρ‹ΠΌ вычислСниям. Π§Ρ‚ΠΎΠ±Ρ‹ Π΄Π°Ρ‚Ρ‹ Π½Π΅ ΠΈΠ½Ρ‚Π΅Ρ€ΠΏΡ€Π΅Ρ‚ΠΈΡ€ΠΎΠ²Π°Π»ΠΈΡΡŒ ΠΊΠ°ΠΊ части Ρ„ΠΎΡ€ΠΌΡƒΠ», ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΡƒΠΉΡ‚Π΅ Ρ„ΡƒΠ½ΠΊΡ†ΠΈΡŽ DATE, Π½Π°ΠΏΡ€ΠΈΠΌΠ΅Ρ€ DATE (1954;7;20) ΠΈΠ»ΠΈ помСститС Π΄Π°Ρ‚Ρƒ Π² ΠΊΠ°Π²Ρ‹Ρ‡ΠΊΠΈ ΠΈ ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΡƒΠΉΡ‚Π΅ Π½ΠΎΡ‚Π°Ρ†ΠΈΡŽ ISO 8601, Π½Π°ΠΏΡ€ΠΈΠΌΠ΅Ρ€ "1954-07-20". Π˜Π·Π±Π΅Π³Π°ΠΉΡ‚Π΅ использования Ρ„ΠΎΡ€ΠΌΠ°Ρ‚ΠΎΠ² Π΄Π°Ρ‚Ρ‹, зависящих ΠΎΡ‚ Π»ΠΎΠΊΠ°Π»ΡŒΠ½Ρ‹Ρ… настроСк, Ρ‚Π°ΠΊΠΈΡ… ΠΊΠ°ΠΊ "07/20/54", расчёт ΠΌΠΎΠΆΠ΅Ρ‚ привСсти ΠΊ ошибкам, Ссли Π΄ΠΎΠΊΡƒΠΌΠ΅Π½Ρ‚ Π·Π°Π³Ρ€ΡƒΠΆΠ΅Π½ Π² соотвСтствии с ΠΈΠ½Ρ‹ΠΌΠΈ Π»ΠΎΠΊΠ°Π»ΡŒΠ½Ρ‹ΠΌΠΈ настройками.


tip

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 - LibreOffice Calc - Formula, button Details... in section "Detailed Calculation Settings", Conversion from text to number list box.


Example

How many workdays fall between December 15, 2016 and January 14, 2017? Let the start date be located in C3 and the end date in D3. Cells F3 to J3 contain five (5) holidays for Christmas and New Year in date format: December 24, 2016; December 25, 2016; December 26, 2016; December 31, 2016; and January 1, 2017.

=NETWORKDAYS.INTL(C3;D3;;F3:J3) returns 21 workdays with default for weekend days.

=NETWORKDAYS.INTL(C3;D3;11;F3:J3) returns 24 workdays with Sunday only weekends.

Alternatively, use the weekend string β€œ0000001” to define Sunday as the non-working day of every week.

=NETWORKDAYS.INTL(C3;D3;β€œ0000001”;F3:J3) returns 24 workdays with Sunday only weekend.

The function can be used without the two optional parameters – weekday and holidays – by leaving them out:

=NETWORKDAYS.INTL(C3;D3) gives 22 working days.