Documentation/Calc Functions/NETWORKDAYS.INTL

    From The Document Foundation Wiki
    This page contains changes which are not marked for translation.
    Other languages:


    Function name:

    NETWORKDAYS.INTL

    Category:

    Date and Time

    Summary:

    Calculates the net number of work or business days between two dates. Holidays (or other non-work days) can be specified and the standard workweek can be redefined.

    Syntax:

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

    Returns:

    Returns an integer value which is the number of work or business days in the given period, excluding defined holidays and weekend days. The start and end dates of the period are counted unless they are defined as holidays or weekend days.

    Arguments:

    StartDate is the date (in quotation marks) or a date-time serial number, or a reference to the cell containing one of those types, which is either the start date or the end date of the period for which the calculation is to be carried out.

    EndDate is the date (in quotation marks) or a date-time serial number, or a reference to the cell containing one of those types, which is either the end date or the start date of the period for which the calculation is to be carried out.


    Weekend is a number or a string, or a reference to the cell containing one of those types, that is used to define the standard workweek to be used. The available numeric options provide a simple way to define the workweek, with the list of possible values given in the following table. Values 1 to 7 correspond to workweeks with two non-work days, while values 11 to 17 correspond to workweeks with one non-work day.

    Number Non-Work Days
    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

    Using a string is a more flexible method to define the workweek. The string must comprise seven characters – '0' is used to indicate a workday and '1' is used to indicate a non-work day. Each character corresponds to a day of the week, starting with Monday. For example, the string "0100011" would define Tuesday, Saturday, and Sunday as non-work days.

    Holidays is a list of dates corresponding to holidays that are not to be counted as workdays. This list can be passed as a range of cells containing date-time serial numbers (possibly displayed in a date format). Alternatively, the list can be passed as an inline array containing either date strings (in quotation marks) or date-time serial numbers. If omitted, no holidays are assumed.

    • If either StartDate or EndDate is not a valid date expression, then NETWORKDAYS.INTL reports a #VALUE! error.
    • If StartDate is earlier than EndDate, the result is a positive integer; if StartDate is later than EndDate, the result is a negative integer.
    • Calc's NETWORKDAYS.INTL ignores any time components of StartDate and EndDate.
    • If a string is used for the Weekend argument, and that string does not contain exactly seven characters, NETWORKDAYS.INTL reports a #VALUE! error. If the string does contain seven characters, but at least one of those characters is neither a '0' nor a '1', then NETWORKDAYS.INTL reports an invalid argument error (Err:502).
    • If a number is used for the Weekend argument, and that number is not an integer value, then NETWORKDAYS.INTL reports an invalid argument error (Err:502). If the number is an integer value but does not lie in either the range 1 to 7 or the range 11 to 17, then NETWORKDAYS.INTL reports a #VALUE! error.
    • If Weekend is passed as a cell reference and that cell contains an error code (such as #NAME?), that error status will be propagated as the result of NETWORKDAYS.INTL.
    • If Holidays is passed as an inline array, and any entry of that array is neither a date nor a date-time serial number, then NETWORKDAYS.INTL reports a #VALUE! error.
    • If Holidays is passed as a cell range, then in most cases cells that do not contain a date or a date-time serial number lying within the relevant period are ignored. However, if a cell contains an error code (such as #NAME?), that error status will be propagated as the result of NETWORKDAYS.INTL.

    Additional details:

    Details specific to NETWORKDAYS.INTL function

    NETWORKDAYS.INTL is inclusive, counting the first and last days of the period (see #Examples:).


    General information about Calc's Date and Time functions

    Note pin.svg

    Note:
    For convenience the information in this subsection is repeated on all pages that describe functions in the Date and Time category.

    • Internal to Calc, a date/time value is handled as a real number and this is sometimes referred to by the term date-time serial number. For example, 2021-02-08 12:00:00 corresponds to the date-time serial number 44235.5. The integer part of a date-time serial number (before the decimal point) is the number of days since a defined start date. The fractional part of a date-time serial number (after the decimal point) is the time of day expressed as a fraction of a day. For example, 0.25 is equivalent to 06:00:00 and 0.75 is equivalent to 18:00:00.
    • Calc uses 1899-12-30 as the default start date (day 0) but you can go to Tools ▸ Options ▸ LibreOffice Calc ▸ Calculate to select alternative start dates; however, unless you have specific requirements for compatibility with legacy spreadsheet applications, we recommend that you do not change this setting. If you copy and paste cells containing date values between different spreadsheets, check that both documents are set to use the same start date to avoid unexpected changes to the dates displayed.
    • Negative date-time serial numbers correspond to dates earlier than Calc's start date. Calc calculates all dates back to 15th October 1582 using the Gregorian calendar. The day before this is 4th October 1582, and Calc uses the Julian calendar for earlier dates.
    • A date-time serial number is the same as any other number, except that we usually choose to display it formatted as a date/time. You can enter a number in a cell and then change the cell’s formatting to display the value as a date/time. You can also enter a date/time in a cell and then change the cell’s formatting to display the date-time serial number.
    • For clarity, the descriptions of the Calc functions within these wiki pages use the standard ISO 8601 format for date and time because this should be independent of your selected locale settings. The default date format on your computer may be different. When entering dates as part of formulas, slashes or dashes used as date separators may be 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 either use the DATE function or place the date in quotation marks and use the ISO 8601 notation. Avoid using locale dependent date formats which may produce errors if the document is loaded under different locale settings.
    • Time zone information is not used by Calc’s Date & Time functions.
    • If you only wish to enter two digits of the year (for example 21 instead of 2021), Calc needs to know whether you mean 2021 or 1921. Go to Tools ▸ Options ▸ LibreOffice ▸ General to view or adjust the range of years which Calc will use to interpret two-digit year values.
    • Calc contains logic to decide how to treat text when encountered as an operand in an arithmetic operation or as an argument to a function that expects a number instead. In this context, unambiguous conversion is possible for ISO 8601 dates and times in their extended formats with separators. For example, the simple formula =1 + "2021-02-08" will usually give the result 2021-02-09 (when formatted as a date). However, this logic may be affected by the settings on the Detailed Calculation Settings dialog, accessed by selecting Tools ▸ Options ▸ LibreOffice Calc ▸ Formula and pressing the Details button in the Detailed Calculation Settings area. If a #VALUE! error occurs, it may be because the Generate #VALUE! error option is selected, in which case you should experiment with one of the other self-explanatory options in the Conversion from text to number drop-down.

    Examples:

    Data for examples
    A B C D E F G H
    1 Start/End Dates   Holidays   Weekend
    2 2020-01-01 Formatted as text, without double quotation marks 0000011
    3 2020-12-31 2020-01-01
    4 2020-04-10 Formatted as General number 11
    5 2020-04-13
    6 2020-05-08
    7 2020-05-25
    8 2020-08-31
    9 2020-12-25
    10 2020-12-28
    Formula Description Returns
    =NETWORKDAYS.INTL("2021-02-18"; "2021-02-19") Returns 2 working days, based on the start and end dates given, no holidays, and the default five-day workweek. This simple example shows the inclusive nature of the count. 2
    =NETWORKDAYS.INTL("2020-12-31"; "2020-01-01") Working days during the year 2020. The negative value reflects that StartDate is later than EndDate. −262
    =NETWORKDAYS.INTL("2020-01-01"; "2020-12-31"; 5) A five-day workweek (with Wednesday and Thursday as non-work days). 260
    =NETWORKDAYS.INTL("2020-01-01"; "2020-12-31";; {"2020-01-01"; "2020-04-10"; "2020-04-13"; "2020-05-08"; "2020-05-25"; "2020-08-31"; "2020-12-25"; "2020-12-28"}) Eight specified holiday dates, and default five-day workweek. An extra separator is included to indicate that the Weekend argument has been omitted. 254
    =NETWORKDAYS.INTL(B2; B3; H2; E3:E10) Equivalent to the previous example, using the above data table; cell H2 contains the value 0000011, formatted as text value without double quotation marks. 254
    =NETWORKDAYS.INTL(B2; B3; H4; E3:E10) Similar to the previous example, but the Weekend consists on Sundays only; cell H4 contains the numeric value 11. 306
    =NETWORKDAYS.INTL("2020-01-01"; "2020-12-31"; "0000111") A four-day workweek (Monday to Thursday). 210
    =NETWORKDAYS.INTL(43831; 43861; "1000111"; { 43845; 43852 }) 43831, 43861, 43845 and 43852 are the date-time serial numbers for the dates 2020-01-01, 2020-01-31, 2020-01-15 and 2020-01-22 respectively. Two holidays within the period, and a three-day workweek (Tuesday, Wednesday, and Thursday). 12

    Related LibreOffice functions:

    NETWORKDAYS

    NETWORKDAYS_EXCEL2003

    ODF standard:

    None.

    Related (or similar) Excel functions:

    NETWORKDAYS.INTL since v.2010.