Ask Your Question
0

Specifying holidays for NETWORKDAYS in-line

asked 2018-08-09 11:45:52 +0200

stanton gravatar image

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 flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2018-08-09 12:05:06 +0200

stanton gravatar image

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

From https://help.libreoffice.org/Calc/Arr...:

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.

edit flag offensive delete link more
0

answered 2018-08-10 01:46:36 +0200

m.a.riosv gravatar image

updated 2018-08-10 01:46:55 +0200

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"})
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-08-09 11:45:52 +0200

Seen: 23 times

Last updated: Aug 10