Description: Cells produce a Validity error if A3 and C3 if they are changed from =TODAY() to =TODAY()+3 This change should be allowed as the value would be greater than or equal to A2 and C2, respectively. If A3 is changed is fixed to today's date with Ctrl+; today's date is shown. However, retyping into A3 =TODAY() also produces a validity error so the former value can no longer be entered. Typing into cell E3 =TODAY()+3 does work; hence, it seems to be a problem with the combination of: Validity... and the function TODAY() Steps to Reproduce: A3 and C3 cells produce a Validity error if changed from =TODAY() to =TODAY()+3 This change should be allowed as the value would be greater than or equal to the values in A2 and C2, respectively. If A3 is fixed to today's date with Ctrl+; today's date is shown. However, retyping into A3 =TODAY() also produces a validity error so the former value can no longer be entered. Typing into cell E3 =TODAY()+3 does work; hence, it seems to be a problem with the combination of: Validity... (Menu Bar > Data > Validity...) and the function TODAY() Thank you Actual Results: TODAY() function and Validity... combination does not work Expected Results: TODAY() function and Validity... combination should work Reproducible: Always User Profile Reset: No Additional Info: Will attach spreadsheet to this issue as unsure how to do so now. Linux Mint Version: 20.3 Desktop environment: Cinnamon LibreOffice Version: 7.4.0.1 / LibreOffice Community Build ID: 43e5fcfbbadd18fccee5a6f42ddd533e40151bcf CPU threads: 2; OS: Linux 5.4; UI render: default; VCL: gtk3 Locale: en-AU (en_AU.UTF-8); UI: en-US Calc: threaded
Created attachment 181368 [details] Today.ods
Same with any formula, e.g. '=DATEVALUE("2022-01-01")' Maybe Validity does not evaluate formulas before checking the input? FTR, Excel 2016 allows such formulas in validated cells.
(In reply to Mike Kaganski from comment #2) Excel 2000, 2003, 2007, 2010 also allow formulae: =TODAY()+3 Can the status please be set to NEW? Thank you
The input has to match the condition, formula results are not accepted. For that, evaluation of formulas would have to be added.
Created attachment 184573 [details] The example file after turning off the Error Alert This seems to have something to do with the Error Alert being initially enabled. If I turn that off, the =TODAY()+3 is accepted at input time and Tools - Detective - Mark Invalid Data is not circling it either. Version: 7.5.0.1.0+ (X86_64) / LibreOffice Community Build ID: 09848e94d20c067499ad69edf81fa80a45d0a632 CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3 Locale: en-US (hu_HU.UTF-8); UI: en-US Calc: threaded
Balazs Varga committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/5f2d7db094fc0f4e7ae40987c3c6762b11184419 tdf#150098 sc validation: allowing formulas for validity 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.
Should a comment be added to the Release Notes at https://wiki.documentfoundation.org/ReleaseNotes/7.6#Calc ? Perhaps something like: > * Evaluate formula inputs in Validity… (Balázs Varga, allotropia), tdf#150098 This can help with feature discovery, testing and feedback. What do you think? Thank you
Created attachment 184935 [details] The example file in 7.5.0 and master Verified in todays nightly: Version: 7.6.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: df470d9658c202a990b9c75e2a8d6e3fd7a06dfd CPU threads: 14; OS: Windows 10.0 Build 19045; UI render: default; VCL: win Locale: de-DE (hu_HU); UI: en-US Calc: threaded Now the =TODAY()+3 is evaluated before the validity check and the result is accepted as valid. Also other formula is accepted correctly such as: =6-5 =D6-D5 =$Sheet2.D2-$Sheet2.D1 =SUM(D2:D3) =SUM(NumS) // NumS is a named range of 2 cells =SUM(EvenNums) // EvenNums is a database range
(In reply to Óvári from comment #7) > Should a comment be added to the Release Notes at > https://wiki.documentfoundation.org/ReleaseNotes/7.6#Calc ? > I added the above image there as well.
Thank you for the fix and the image in the release notes. Please note the small change in the caption in the release notes. If the correction is incorrect, please feel free to revert the change. Thank you once again.
Balazs Varga committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/d8ae6d1388f28c405c4de2dfe93dbfe2d8acd470 Related: tdf#150098 sc validation: allowing formulas for validity 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.
Balazs Varga committed a patch related to this issue. It has been pushed to "libreoffice-7-5": https://git.libreoffice.org/core/commit/d86aa3a0711a09aeae752086f8fdf5e89b552ec5 tdf#150098 sc validation: allowing formulas for validity test It will be available in 7.5.2. 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.
Balazs Varga committed a patch related to this issue. It has been pushed to "libreoffice-7-5": https://git.libreoffice.org/core/commit/a5e765e67ef6527486771caaf6c89962136ec07e Related: tdf#150098 sc validation: allowing formulas for validity test It will be available in 7.5.2. 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.
Verified fixed by entering/changing formula in cell $Range.A3 in the following spreadsheet: "Statement Generator" https://extensions.libreoffice.org/en/extensions/show/21514 "Számlakivonat létrehozása" https://extensions.libreoffice.org/hu/extensions/show/21514 Thank you Version: 7.5.3.2 (X86_64) / LibreOffice Community Build ID: 9f56dff12ba03b9acd7730a5a481eea045e468f3 CPU threads: 12; OS: Linux 5.19; UI render: default; VCL: gtk3 Locale: en-AU (en_AU.UTF-8); UI: en-US Flatpak Calc: threaded