Specifying holidays for NETWORKDAYS in-line

I would like to use NETWORKDAYS to calculate the number of working days between two dates, not counting public holidays.

Help mentions how to use a range of cells as a source for these days. I have found I can specify a single date like this:

=NETWORKDAYS(D16,D12,DATE(2018,8,15))


which will correctly reduce the number of working days by one. However, I haven’t been able to figure out how to specify more than one date in the formula.

What do I do if I have no range of cells but would like to specify multiple dates in the formula itself?

edit retag close merge delete

Sort by » oldest newest most voted

The @stanton inner array answer can be a solution, but inner arrays can't have formulas neither with functions, so only direct values like text and numbers, dates can be entered as text and with those the best it's use ISO format so no issue with the underlying language.

=NETWORKDAYS("2018-08-01";"2018-08-31";{"2018-08-31";"2018-08-14"})

more

LibreOffice does not support this in conjunction with the DATE() function.

Calc supports inline matrix/array constants in formulas. An inline array is surrounded by curly braces '{' and '}'. Elements can be each a number (including negatives), a logical constant (TRUE, FALSE), or a literal string. Non-constant expressions are not allowed.

Therefore, using DATE() in array elements would not work.

As a workaround, since dates eventually translate to numbers, one could determine the numbers which correspond to the desired dates and enter these like:

=NETWORKDAYS(D16,D12,{42;48;96})


Downside is that the function will look quite cryptic and be hard to debug, therefore using a cell array is recommended wherever possible.

more