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).
please attach your file here
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.
Sample file uploaded
I can't repro this from scratch
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()?
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
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.
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.
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 18.104.22.168
'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.
I can confirm with Version: 22.214.171.124.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.
(In reply to raal from comment #11)
> I can confirm with Version: 126.96.36.199.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.
I've tested further as follows:
Confirmed with LO running on MacOS.
Version: 188.8.131.52 / 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
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: 184.108.40.206 (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
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.
Footnote to this: I updated to v220.127.116.11 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.