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 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.
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.
(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.
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.
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.
I had exactly the same error on my W10 PC with a .xlsx spreadsheet created about two years ago in a much older version of LO. I run the more stable version of LO and currently have 7.1.8.1. I noticed this fault 2 or 3 months ago. All the instances of EOMONTH in an annual energy use spreadsheet converted to the longer com.sun.star.sheet.addin.Analysis.getEomonth as described in "Calling internal CALC functions in BASIC" in the help pages. The conclusion by Peter Johnson on 06-10-21 that this only affects the MAC version does not seem to be correct. The cell formula continues to work but it is not easy to maintain and I have spent some time re-entering them as plain EOMONTH. I could not tell you how to reproduce this because it is some sort of corruption of an existing formula. I do have monthly backups of the file and if it would help I could look back to see when the corruption occurred and maybe tie it in to the version of LO in use at the time as I have a record of when I installed each version
I have checked back and I installed LO 7.1.5.2 on 04/09/21. The corruption started to occur after that.
I can not confirm this Do you still have the issue on fresh documents created with LO 7.3? Setting OS to all given the report in comment 15 Version: 7.3.0.3 / LibreOffice Community Build ID: 0f246aa12d0eee4a0f7adcefbf7c878fc2238db3 CPU threads: 10; OS: Mac OS X 12.2; UI render: Skia/Metal; VCL: osx Locale: en-US (en_US.UTF-8); UI: en-US Calc: threaded
Following my comment on 26-12-21 this corruption of the EOMONTH function has suddenly re-appeared after I had corrected the formulae. The spreadsheet was created over 2 years ago (version unknown). I recently upgraded to ver 7.2.5.2 from 7.2.8.1 which I had been using since 9-12-21. Don't know if this is significant all instances of: =DAY(EOMONTH(Ann,0)) in 3 different sheets within the file have become: =DAY(com.sun.star.sheet.addin.analysis.geteomonth(Ann,0)) Cannot say how to reproduce this. The corruption seems to happen at random. I update this spreadsheet regularly with energy usage data and suddenly see cells with #NAME? which have been corrupted.
Could everyone who can reproduce this somehow (*not* by loading an existing erroneous file but creating from scratch and reloading) please state the Locale information that can be obtained from Help -> About, thanks.
And also the LO version of course and operating system, thanks again.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/9aab475e221e1abee2108b21545f2bdaa48552e6 Related: tdf#142293 tdf#141495 Allow additional symbols in externals map It will be available in 7.5.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.
The last commit 9aab475e221e1abee2108b21545f2bdaa48552e6 allows reading and repairing such broken documents, but the cause why those programmatic names were written is unidentified.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-4": https://git.libreoffice.org/core/commit/e54ec5412d9196fb37979db2865b0305bc3d4ad1 Related: tdf#142293 tdf#141495 Allow additional symbols in externals map It will be available in 7.4.0.0.beta2. 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.
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/5d30e9e101ae8167c33fe24ca12d8d0685f9f8fb tdf#142293, tdf#141495: sc_subsequent_filters_test2: Add unittest It will be available in 7.5.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 bug has been marked as a duplicate of bug 142293 ***