Steps to reproduce: 1. Create an empty calc sheet 2. In A1, put formula "=NOW()" 3. In A2, put formula "=NETWORKDAYS(NOW();NOW()+6;A1)" (see that result is 4) 4. Clear A1 Expected result: A2 should change to 5 Actual result: A2 becomes #VALUE! It worked as expected in 4.4 -> regression, adjusting severity appropriately. Tested with Version: 5.0.1.2 (x64) Build ID: 81898c9f5c0d43f3473ba111d7b351050be20261 Locale: ru-RU (ru_RU) under Win7x64
Reproducible with Verze: 5.0.1.2 ID sestavení: 81898c9f5c0d43f3473ba111d7b351050be20261, win7 Works with Version 4.0.0.3 (Build ID: 7545bee9c2a0782548772a21bc84a9dcc583b89)
A first quick glance indicates that the problem was introduced with commit 111952dccc1bf9e28e61c0233816248c848cbf53 (fdo#77985 make calc function NETWORKDAYS comply with ODFF1.2) I will correct this.
The problem is connected with the changed handling of empty cells in arrays, see bug 41481. Working on a proper fix.
Created attachment 118599 [details] some uses of NETWORKDAYS (In reply to Winfried Donkers from comment #3) > The problem is connected with the changed handling of empty cells in arrays, > see bug 41481. > Working on a proper fix. And the tricky part is this: technically the result #VALUE! is correct when argument3 (sequence of holidays) points to an invalid array. In version before 4.4 an empty value was accepted, but this is not strictly correct. With version 5, this has been fixed. I am not sure yet if this behaviour is to be regarded as a bug (which I will then fix) or 'as defined in ODFF1.2, (http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1018198_715980110)
@Eike: Can you give your views with respect to this behaviour? SCInterpreter::GetSortArray() sets nGlobalValue to errNoValue when step 4 of the description is executed (see attachment 118599 [details]). This is IMO correct behaviour. The question is, should I set nGlobalValue back to 0 in this case, or is the use of a reference to an empty array not to be accepted? What is the behaviour of Excel in this case?
Created attachment 118600 [details] NETWORKDAYS with empty third param in MS Excel 2013 (In reply to Winfried Donkers from comment #5) > What is the behaviour of Excel in this case? It doesn't give an error, it works as expected. Actually, I don't see requirement to throw error on empty array at the link you cited. It feels natural to be able to define a (named) range, say, "Holidays", and use it as third parameter; then fill it as appropriate, and leave empty if needed.
(In reply to Mike Kaganski from comment #6) > It doesn't give an error, it works as expected. Thank you for your fast feedback > Actually, I don't see requirement to throw error on empty array at the link > you cited. See http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1017866_715980110 > It feels natural to be able to define a (named) range, say, "Holidays", and > use it as third parameter; then fill it as appropriate, and leave empty if > needed. I understand your feelings. That's why I called it 'tricky' in comment 4 .
(In reply to Winfried Donkers from comment #7) > See > http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2. > html#__RefHeading__1017866_715980110 I followed every clause of 3.3 Non-Scalar Evaluation (aka 'Array expressions'), and still cannot find a place that would cover this case. Both Parts 1 and 2 are about functions taking scalar where a matrix is passed, and discuss which cell of passed matrix should be used as actual argument to the function in specific position. Part 2.2 describes how to process when (different-sized) matrices are passed as arguments that are expected to combine with each other. Example in Note 13 gives a good example for that. The NETWORKDAYS problem lies in a different area: it expects a matrix in its third argument, and the size of array isn't required to correspond to something in any way; its function is only to extract those dates that are in the range of bounding dates and not weekends, and to subtract their number from scalar result. If the rmatrix contain empty cells, but at least one cell is non-empty (but even if it is outside of dates range), current implementation works OK: A1: 2001-01-01. B1: empty A2: =NETWORKDAYS(NOW();NOW()+6;A1:B1) A2 -> 5 Here A1 is outside the range, and B1 is empty, still function works. I beleive that all-empty-cells array should be equally legal.
Actually http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#NETWORKDAYS has the third parameter type defined as DateSequence, which is unrelated to non-scalar aka array expressions. A sequence can consist of zero elements, e.g. SUM(X11:X22) can be empty. Only a few functions treat empty sequences special. For DateSequence see also http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1017996_715980110 and previous sections.
Winfried Donkers committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=75bde904d5b4f756037889f2b2ddee3e34dd81b8 tdf#94079 allow empty array for holiday sequence It will be available in 5.1.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Winfried Donkers committed a patch related to this issue. It has been pushed to "libreoffice-5-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=04ef6cf59052010cb6103e260dff6367b634acd8&h=libreoffice-5-0 tdf#94079 allow empty array for holiday sequence It will be available in 5.0.3. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Supriyo Paul committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/e9697e315cd382ce14b5c8efc47a07aa06dea30c tdf#94079 sc_ucalc: Add unit test It will be available in 7.6.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
(In reply to Commit Notification from comment #12) > Supriyo Paul committed a patch related to this issue. > It has been pushed to "master": > > https://git.libreoffice.org/core/commit/ > e9697e315cd382ce14b5c8efc47a07aa06dea30c > > tdf#94079 sc_ucalc: Add unit test > > It will be available in 7.6.0. > > The patch should be included in the daily builds available at > https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More > information about daily builds can be found at: > https://wiki.documentfoundation.org/Testing_Daily_Builds > > Affected users are encouraged to test the fix and report feedback. This test fails for me if I run it on Saturday or Sunday, but succeeds for all other days. Please use fixed date for the test. I need to wait till Monday in order to let Jenkins be happy with my commit :-(
Noel Grandin committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/2fccec4078f9cdcfa42447c2217d52035dc8fc3e Revert "tdf#94079 sc_ucalc: Add unit test" It will be available in 7.6.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
(In reply to Laurent Balland from comment #13) > (In reply to Commit Notification from comment #12) > > Supriyo Paul committed a patch related to this issue. > > It has been pushed to "master": > > > > https://git.libreoffice.org/core/commit/ > > e9697e315cd382ce14b5c8efc47a07aa06dea30c > > > > tdf#94079 sc_ucalc: Add unit test > > > > It will be available in 7.6.0. > > > > The patch should be included in the daily builds available at > > https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More > > information about daily builds can be found at: > > https://wiki.documentfoundation.org/Testing_Daily_Builds > > > > Affected users are encouraged to test the fix and report feedback. > > This test fails for me if I run it on Saturday or Sunday, but succeeds for > all other days. Please use fixed date for the test. I need to wait till > Monday in order to let Jenkins be happy with my commit :-( Hi Laurent, Sorry for the inconvenience I caused. Thank you so much for your information, I will update the patch as soon as possible..
(In reply to Supriyo Paul from comment #15) > Sorry for the inconvenience I caused. Thank you so much for your > information, I will update the patch as soon as possible.. No need to be sorry, these things happen to all of us. It is reverted, and so is no more a blocker; I am looking forward to see the new unit test commit from you. Looking how the older one was implemented, I suggest you to use fixed dates in ISO format in the calls to ScDocument::SetString, instead of the NOW() spreadsheet function. Thank you for your contribution!