Description: Topic: EDITING / FILESAVE (?) In some cases / some files, the LO Calc date functions YEAR, MONTH and DAY behave erratically with respect to the base date: when the setting is 1904-01-01, they mostly work correctly, but sometimes assume the default 1899-12-31 setting. (The dates are always displayed correctly, according to the base date setting.) Steps to Reproduce: Not always reproducible - some files OK, some obviously wrong. See attachments: affected file & screenshots of the steps below. 1) Date preference = 1904-01-01. 2) Year-Month-Day calculations in file are false, with a 4-year-1-day error. 3) Upon entry of another value, the corresponding calculated year-month-day is OK. 4) Force recalculate of whole file. 5) After recalculation, the previously correct entered line is again false. Actual Results: When calculating the whole file, the date functions obviously sometimes apply the 1899-12-31 system, although the preference is set for 1904-01-01. Strangely, however, they work correctly when entering data! Also, the displayed dates are always correct in the 1904 system. Expected Results: The date functions (YEAR, MONTH, DAY) should reliably apply the selected base date upon recalculating the file, as they do when entering data (step 3), instead of behaving somewhat erratically. Reproducible: Sometimes User Profile Reset: Yes Additional Info: - See affected attached file ""LO Calc date base problem.ods" + screenshots. - The problem is confirmed in safe mode - this is not a user profile issue. - Not all files are affected. - The first time I saw the problem was with 25.2.1.2. Same with 25.2.2.2. - There is a workaround: setting the base date preference to 1899-12-31. 6) Strangely, recopying the problematic data & formulae elsewhere (copy-paste) in the worksheet generates a working data set - now, upon recalculating, columns B-D still behave badly, but the copied data & formulae in column I-L work properly! 7-8) Upon setting the base date to 1899-12-31, all works fine. 9-10) Upon returning the base date to 1904-01-01, again bad (A-D) / good (I-L). Regression: - The problem lies with the base date, it is not just some "wrong calculation". - The facts that a) some cells are affected and some not in the same file, b) that calculation is always correct upon entry, but not upon recalculation of the spreadsheet, and c) there sometimes is an obvious conflict between the displayed date and the year-month-day breakdown using the date functions, point to some sort of local storage issue / erratic behaviour in the date functions rather than a global problem. An analysis of the file could be helpful in this regard.
Created attachment 200418 [details] Affected LO Calc file The affected spreadsheet (see screenshots for the steps in the description).
Created attachment 200419 [details] 01) Basedate preference = 1904-01-01
Created attachment 200420 [details] 02) Wrong date breakdown using YEAR-MONTH-DAY functions
Created attachment 200421 [details] 03) Calculation correct upon entry of another date
Created attachment 200422 [details] 04) Force complete recalculation of worksheet
Created attachment 200423 [details] 05) Calculations with wrong base date after recalculation of sheet
Created attachment 200424 [details] 06) Copy-paste of some data - now both good & bad in same file!
Created attachment 200425 [details] 07) Base date set to 1899-12-31
Created attachment 200426 [details] 08) Confirm workaround - all OK in this mode
Created attachment 200427 [details] 09) Base date set again to 1904-01-01 to check bug persistence
Created attachment 200428 [details] 10) After setting back the base date to 1904-01-01, again split bad/good behaviour The persistence of the bug upon toggling the base date between 1904-01-01 and 1899-12-31 and the coexistence of correct and incorrect calculations in the same file hint at some sort of subtle file corruption (not the data itself - see next screenshot, but the way it is handled).
Created attachment 200429 [details] 11) Dates displayed as serial numbers - 1 entered & recalculated Here, the display is set to number format (still with 1904-01-01 base date), to confirm that the underlying data is the same. Also, 1 is entered in the top row. As in step 3, the initial result is correct in both places (1904-01-02). The screenshot shows the status after forced recalculation of the worksheet, with 1899-12-31 displayed incorrectly in the left part, as in step 5.
Ok, a lot of images, but not a sample file that shows the issue. Please attach a sample file, reduce the size as much as possible without private information, and paste the information in Menu/Help/About LibreOffice, there is a copy icon.
(In reply to m_a_riosv from comment #13) > Ok, a lot of images, but not a sample file that shows the issue. > > Please attach a sample file, reduce the size as much as possible without > private information, > and paste the information in Menu/Help/About LibreOffice, there is a copy > icon. The affected LO Calc sample file is the first attachment, labelled "Affected LO Calc file". I attached the step-by-step screenshots as a reference, as I'm not sure the bug will be reproducible on all installations - maybe everything will look OK when you open it.
Just confirmed the exact same behaviour of the attached file on a fresh install of LO 25.2.2.2 on another machine. - First report: MacBook Pro, Mid-2015, Mac OS 12.7.6 Monterey - Confirmed: iMac 18.3, Mid-2017, Mac OS 13.7.5 Ventura (Exactly same cells with bad and good behaviour on both systems: always correct calculation upon data entry, but return to incorrect values upon forced recalculation of the whole sheet.)
Created attachment 200431 [details] Screeshot at opening the file without any modification. Sorry, I see now. Looks fine for me at opening. Version: 25.2.3.1 (X86_64) / LibreOffice Community Build ID: d8d1af5f77df955194e52baabe19324532ac8e8b CPU threads: 16; OS: Windows 11 X86_64 (10.0 build 26100); UI render: Skia/Raster; VCL: win Locale: en-US (es_ES); UI: en-US Calc: CL threaded
(In reply to m_a_riosv from comment #16) > Created attachment 200431 [details] > Screeshot at opening the file without any modification. > > Sorry, I see now. > > Looks fine for me at opening. > Version: 25.2.3.1 (X86_64) / LibreOffice Community > Build ID: d8d1af5f77df955194e52baabe19324532ac8e8b > CPU threads: 16; OS: Windows 11 X86_64 (10.0 build 26100); UI render: > Skia/Raster; VCL: win > Locale: en-US (es_ES); UI: en-US > Calc: CL threaded Good that you could confirm the issue between the different (left & right) parts of the file. - Is the calculation also correct upon manual entry of another date in column A? - Does the problem persist / reappear when you force recalculation of the sheet? - Does the problem also disappear when you set the base date to 1899-12-31?
Created attachment 200435 [details] Screens with 1899-12-30 > > Good that you could confirm the issue between the different (left & right) > parts of the file. > - Is the calculation also correct upon manual entry of another date in > column A? Yes > - Does the problem persist / reappear when you force recalculation of the > sheet? No issue > - Does the problem also disappear when you set the base date to 1899-12-31? ?
Created attachment 200437 [details] After setting the base date to 1899-12-31, before forcing recalculation. After setting the base date to 1899-12-31, I get the same behaviour: the left side is OK, but the right side is wrong. Obviously, LO - wrongly - assumes the file must not be recalculated.
Created attachment 200438 [details] Now forcing recalculation. …I therefore need to force a recalculation to update the results.
Created attachment 200439 [details] After forcing recalculation in 1899-12-31 mode. In 1899-12-31 mode, after forced recalculation, the results are now all correct (both A-D and I-L).
Can confirm Version: 25.2.2.2 (X86_64) / LibreOffice Community Build ID: 7370d4be9e3cf6031a51beef54ff3bda878e3fac CPU threads: 8; OS: Linux 6.11; UI render: default; VCL: gtk3 Locale: en-US (en_US.UTF-8); UI: en-US Flatpak Calc: threaded Version: 25.8.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: f355ddcbf2bf037263e336724829b5467b94ef40 CPU threads: 8; OS: Linux 6.11; UI render: default; VCL: gtk3 Locale: en-US (en_US.UTF-8); UI: en-US Calc: CL threaded
Bibiseted using linux-64-24.8 b38974391e8d4bf0d450abfaa86bbccbe1022995 is the first bad commit Related: tdf#160056 do calc NumberFormatting via ScInterpreterContext author Caolán McNamara <caolan.mcnamara@collabora.com> Fri Mar 22 09:47:47 2024 +0000 committer Caolán McNamara <caolan.mcnamara@collabora.com> Sat Mar 23 01:14:13 2024 +0100 Adding to CC: Caolán McNamara Proposed patch to fix: https://gerrit.libreoffice.org/c/core/+/184687
Mateusz Wlazłowski committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/234cb2da3598837a60572dc8475bcb7d9503335f tdf#166268 Initialize the correct null date in ImpSvNumberformatScan It will be available in 25.8.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.
Mateusz Wlazłowski committed a patch related to this issue. It has been pushed to "libreoffice-25-2": https://git.libreoffice.org/core/commit/635c4eb84949a4cc34dadc079a1d65371da39a93 tdf#166268 Initialize the correct null date in ImpSvNumberformatScan It will be available in 25.2.4. 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.
Mateusz Wlazłowski committed a patch related to this issue. It has been pushed to "libreoffice-24-8": https://git.libreoffice.org/core/commit/3f95aa87300152a1fb85815599c9588dc953e302 tdf#166268 Initialize the correct null date in ImpSvNumberformatScan It will be available in 24.8.8. 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.
Thanks Mateusz