Created attachment 122450 [details] Spreadsheet showing birthdays I created a spreadsheet for advising dates of family birthdays/marriages, and days to next (attached). This works correctly when opened in Excel. When opened in Libre Office Calc, it shows a curious error in the last three rows, column G (-35,927 days to next anniversary!). Try it. John
Hi John, What I see is that the problem is in the H column: =IF(C53>0, DATE(0,MONTH(C53),DAY(C53)), "") I believe it's the 0 in the DATE field that is creating the issue. Can you attach a pdf export from Excel (with all columns visible) so that those of us without Excel can easily compare? Throwing this to NEEDINFO, if you can get us that PDF please set to UNCONFIRMED. Thanks
Hi @John, LibreOffice has an option about how to deal with two digits years, in this case interpreted so when you enter '0' as year. Menu/Tools/Options/LibreOffice/General/Year (two digits). change the first year to 1900 and doing a hard recalc [Ctrl+Shift+F9] works as you like. But why enter '0' if you want '1900', being explicit it's always better and clear, and in this case no memory is saved.
@m.a.riosv Thoughts on whether we should change the default to match Excel - even if not a bug might be good for interop purposes. I have no strong feelings either way and I believe it might break other spreadsheets....
@Joel, I don't remember a question about this matter. I have not excel, so I don't know if there is an option like LibreOffice has, may be is there, LibreOffice is not the only one with such option. In fact there is an option in Win10, I think similar in other Win, in the date&time configuration. OTOH I think the option is mainly for when you are going to introduce a lot of data, but in a formula, that you are going to copy down, don't use explicit year haven't too much sense for me, what will do when there will be people born in 2000's.
Thanks for the above replies. I have been experimenting with various modifications to my spreadsheet to try to identify/localise this error, with no success. Note that in the function: DATE(0,MONTH(Cx),DAY(Cx)) the first argument is '0' (zero), not YEAR(0). i.e. this function should return the number of days from the start of the relevant year for the date in Cx (and each Cx ,x+1, x+2...) as appropriate. Excel does this correctly for every row. Libre Calc does it correctly for most rows, but not the last three. What's wrong? John
Date entered with 2 digits for years depends on regional settings of Windows. Unless Windows date is changed in Control panel, Excel use the same rules as Calc. < 30 : 20th century >= 30 : 21th century See https://support.office.com/en-us/article/Change-the-date-system-format-or-two-digit-year-interpretation-e6354061-6c98-4e17-84b4-f122dc6063a7#__toc298144367 and Settings in Control panel : https://support.microsoft.com/en-us/kb/214391
It's 16 years after Y2k and people still use 2-digit years in non-manual data input? We will not fix your document's data. If you need a different support of 2-digit years then use the setting already mentioned, under Tools->Options->LibreOffice->General "Year (Two Digits)"