If a Calc file using the EASTERSUNDAY() function [say, =EASTERSUNDAY (2011)] is saved in .XLS or .XLS format either by LibreOffice or Ooo, then loaded, cells with EASTERSUNDAY() and those referencing them show as #MACRO? On retyping the formula exactly as shown it displays correctly; saving it in LO format preserves it, saving as .XLS(S) repeats the same behaviour. Note: EASTERSUNDAY() is not a function supported by Microsoft Excel (2000 version at least, later ones too I think), I think this problem wasn't present in recent versions of Ooo, not sure. That's the end of the report. Why I use this: I have a spreadsheet dealing with public holidays that I distribute to Excel users. There is a long formula that works in Excel, but is wrong in Ooo/LO for 2011 and 2038 (due to a fault in Excel date routines not present in Ooo/LO that is corrected for in the formula). So the spreadsheet detects whether it is running under Excel or LO/Ooo, and uses the long formula in Excel, and EASTERSUNDAY() in Ooo/LO.
[Reproducible] with "LibreOffice 3.3.2 – WIN7 Home Premium (64bit) English UI [OOO330m19 (Build:202 / tag 3.3.2.2)]". Same Problem with OOo 1.1.4, 3.1. dev3.4. It's an import problem, MS EXCEL Viewer shows 'eastersunday()' in sample.xls correctly. I reloaded a sample.xls document, saved as samplenew.ods and compared contents.xml with contents.xml from priginal sample.ods: Correct (sample.ods) -------------------- formula="of:=EASTERSUNDAY(2011)" office:value-type="date" office:date-value="2011-04-24"><text:p>24.04.11</text:p> Wrong samplenew.ods -------------------- formula="of:=EASTERSUNDAY(2011)" office:value-type="date" office:date-value="1899-12-30"><text:p>#MACRO?</text:p> @Kohei: one for you?
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present on a currently supported version of LibreOffice (4.4.0.3 or later): https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) Thank you for your help! -- The LibreOffice QA Team This NEW Message was generated on: 2015-02-19
Error remains unchanged. LO Calc, 4.4.0.3, Win7/32 I opened a blank spreadsheet, entered =EASTERSUNDAY(2011) into a cell, it displayed correctly as a date. I saved the spreadsheet as .XLS, ran LO Calc again, navigated to the file and loaded it. The field displayed as #MACRO? I have not done anything else. This posted in response to an emailed request today. Don't have sensible options for Status: and resolved as boxes, actual status is old, resolved is absolutely NO.
PS to my original report: typo, for ".XLS or .XLS" read ".XLS or .XLSX".
PPS: I keep saving with resolved as set to WONTFIX, it gets changed to FIXED. It's not fixed! Will try INVALID
Not reproducible for me with current master (next 4.5) and LO 4.4.2.0.0+ under Ubuntu 14.10 x86-64. But reproducible if I save the file in .xls format. But if EASTERSUNDAY() function is not supported by MS-Excel, why do you think that is a bug in LO ? Best regards. JBF
"why do you think that is a bug in LO?" Thanks. Yes, that's a good point. While the problem manifests even without Excel, simply saving and loading as .xls, the only real-life reason to save this way is to transfer with Excel, and this could well corrupt the cell anyway. So I withdraw my report (I only followed up now because I was asked to) - marked RESOLVED - NOTABUG. (I would still actually call it technically a bug, though minor, but won't argue.) That really says everything, but I'll explain why this was relevant in case anyone is interested (else stop reading here). A few years ago I was developing a spreadsheet which did a lot of calculations based on Easter, in OpenOffice.org (Ooo). Excel's date routines had (and may still have) errors, and no EASTERDATE function. A long formula which worked round the errors in Excel mostly worked in Ooo, but gave wrong answers for a few years; i.e. it wasn't usable. I worked around this by getting the spreadsheet to detect whether it was running under Excel or Ooo, and use either EASTERDATE or the Excel-only formula accordingly. This worked fine, but if I saved the spreadsheet in .xls(x) format (e.g. for someone to work on in Excel, save, and return), when reloaded into Ooo the EASTERDAY formula (not used in Excel) was generating the #MACRO? error. As Ooo was supposed to be as compatible as possible with Excel, this was and is a clear (if obscure, and unresolvable) incompatibility, and I reported it as such. If I still needed the spreadsheet and full compatibility now, I would probably have to write a complex EasterDate function in LO (that would give some wrong values in Excel, but wouldn't be corrupted in transfer), and get the spreadsheet to detect if it was running under LO or Excel and choose the formula accordingly. Or maybe Excel now handles .ods files with EASTERDATE (it didn't then). Anyway, it's no longer an issue.