Bug 141495 - CONFIGURATION = EOMONTH function corruption
Summary: CONFIGURATION = EOMONTH function corruption
Status: UNCONFIRMED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
7.1.2.2 release
Hardware: All macOS (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: filter:xlsx
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2021-04-05 10:35 UTC by john.warton@virgin.net
Modified: 2021-11-18 14:17 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Spreadsheet from affected file showing error in A3 (4.95 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2021-04-06 19:52 UTC, john.warton@virgin.net
Details
Test files showing error (11.81 KB, application/zip)
2021-09-07 18:03 UTC, Peter Johnson
Details

Note You need to log in before you can comment on or make changes to this bug.
Description john.warton@virgin.net 2021-04-05 10:35:47 UTC

    
Comment 1 john.warton@virgin.net 2021-04-05 10:38:57 UTC
I had an existing spreadsheet, edited in a previous version of CALC using the 'EOMONTH' function.  All instances of the function in cell equations were changed to 'com.sun.star.sheet.addin.analysis.geteomonth' creating an error. Apologies if not reported correctly (I'm a bug newbie).
Comment 2 Roman Kuznetsov 2021-04-05 19:32:51 UTC
please attach your file here
Comment 3 john.warton@virgin.net 2021-04-06 19:52:15 UTC
Created attachment 170986 [details]
Spreadsheet from affected file showing error in A3

The attached file was edited from a a larger file that contained the eomonth error with the additional string preceding the command. Cell A1 is base date and A3 is eomonth expression.
Comment 4 john.warton@virgin.net 2021-04-06 19:53:16 UTC
Sample file uploaded
Comment 5 Roman Kuznetsov 2021-04-06 20:21:14 UTC
I can't repro this from scratch
Comment 6 Eike Rathke 2021-04-07 16:25:58 UTC
What version of LibreOffice was the original document (not the attached excerpt) created with, and was it always saved as .xlxs, or how was that produced? What version of LibreOffice was used to save when reloading the document first resulted in com.sun.star.sheet.addin.analysis.geteomonth()?
Comment 7 john.warton@virgin.net 2021-04-25 09:56:36 UTC
Having just reopened the spreadsheet that had the problem and that I had edited to correct, the same problem has appeared i.e. the eomonth function string has changed to "com.sun.star.sheet.addin.analysis.geteomonth" (the full expression is "=AVERAGE(H46:H64)*(1.005^((DAY(com.sun.star.sheet.addin.analysis.geteomonth(B45,-1))/365))-1)").  I am going to try opening in Excel, resaving and then reopening in CALC to see if the file is corrupted and CALC is unable to resolve the corruption.... will report back when done
Comment 8 john.warton@virgin.net 2021-04-25 10:00:09 UTC
The problem appears to appear when I enter a value (number) in one of my sheet's cells.  i.e. the error is not apparent before changing the number's value.  After entering, the eomonth string changes.
Comment 9 Peter Johnson 2021-06-07 15:08:05 UTC
If it helps narrow down what's happening, I can say I've experienced this:
- only with the EOMONTH function
- only with xlsx files that are being synced with MS Sharepoint
- only with files that I know a colleague sometimes works on too, in his case using MS Excel
- never if I edit the file in Excel; only if I use LO

So my assumption is that there is something about LO and Excel accessing a shared file simultaneously that causes the conversion for this one function to go wrong.
Comment 10 Peter Johnson 2021-09-07 18:03:07 UTC
Created attachment 174866 [details]
Test files showing error

Here's a simple test that shows the issue.

My previous comment about syncing with Sharepoint or another user editing was incorrect.  How I created the file:

Enter start value in cell A5 and EOMONTH formulae in B5:L5 and A7:L7. I didn't format the dates to remove formatting as a possible factor.

Save with .xlsx format.

Open in Excel.

The start value displays but the formulae show a #NAME? error. To recover, it is necessary to Find/Replace the value '@com.sun.star.sheet.addin.Analysis.getEomonth' in all formulae in the workbook in Excel and save. Then it is OK in Excel.

I am uploading a zip with two files:
'Test eomonth convert LO.xlsx' is the original file saved by LO 7.2.0.4
'Test eomonth convert EXCEL.xlsx' is the above file after the find/replace step, and saved by Excel 16.52.

The files look identical in LO, but are the not the same in Excel.
Comment 11 raal 2021-09-17 14:58:27 UTC
I can confirm with Version: 7.3.0.0.alpha0+ / LibreOffice Community
Build ID: 55adeb1c3fbcf32c7c44a3f0c310b56298f551f9
CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: cs-CZ (cs_CZ.UTF-8); UI: en-US
Calc: threaded Jumbo

Works in LO 4.1, regression.

Create file with Eomonth function, save as xlsx, open in excel 2010.
Comment 12 raal 2021-09-17 16:40:21 UTC
(In reply to raal from comment #11)
> I can confirm with Version: 7.3.0.0.alpha0+ / LibreOffice Community
> Build ID: 55adeb1c3fbcf32c7c44a3f0c310b56298f551f9
> CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: gtk3
> Locale: cs-CZ (cs_CZ.UTF-8); UI: en-US
> Calc: threaded Jumbo
> 
> Works in LO 4.1, regression.
> 
> Create file with Eomonth function, save as xlsx, open in excel 2010.

I was wrong, cannot reproduce from scratch.
Comment 13 Peter Johnson 2021-10-06 10:53:18 UTC
I've tested further as follows:

Confirmed with LO running on MacOS.

Version: 7.2.1.2 / LibreOffice Community
Build ID: 87b77fad49947c1441b67c559c339af8f3517e22
CPU threads: 12; OS: Mac OS X 10.16; UI render: default; VCL: osx
Locale: en-GB (en_GB.UTF-8); UI: en-US
Calc: threaded

Create file with EOMONTH function, save as xlsx, open in Excel 16.53 on MacOS.  Cell with EOMONTH function shows #NAME? and string as I reported on 7/9/21.

HOWEVER... with LO running on Windows.

Version: 7.2.1.2 (x64) / LibreOffice Community
Build ID: 87b77fad49947c1441b67c559c339af8f3517e22
CPU threads: 2; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win
Locale: en-GB (en_GB); UI: en-GB
Calc: threaded

Create file with EOMONTH function, save as xlsx (in Windows), copy to Mac system and open in Excel 16.53 on MacOS.  The function shows correctly.

I have confirmed this further with a larger xlsx file with many EOMONTH functions.  If I open the file in LO/MacOS, edit and save, it is corrupted.  The 'getEOMonth' etc string is visible in the formulae in LO/Win (but not in LO/MacOS); the cells show #NAME?.  If I save the file as ODS in LO/MacOS, open the ODS version in LO/Win and then re-save as xlsx, the errors are not present.

Conclusion: the LO version for MacOS only has this fault.  That's borne out in raal's comment 12 - OS was Linux.
Comment 14 Peter Johnson 2021-11-18 14:17:58 UTC
Footnote to this: I updated to v7.2.2.2 on MacOS and the error seemed to have gone.  That continued for about 2 weeks, then the formula conversion started going wrong again.

So I tried a safe mode restart of LO and the error was corrected, even after restarting in normal mode.

I don't have enough data to confirm and am not clear how I could reproduce, but it might suggest a place to look.