Created attachment 187441 [details] XLS in question Open the XLS and look in the "Payment Due Date" column and you see a bunch of #VALUE!. Version: 7.5.2.2 (X86_64) / LibreOffice Community Build ID: 53bb9681a964705cf672590721dbc85eb4d0c3a2 CPU threads: 16; OS: Windows 10.0 Build 19045; UI render: Skia/Vulkan; VCL: win Locale: en-US (en_US); UI: en-US Calc: threaded
In the formula is used the value 112 to set up a year with DATE(), that it's forbidden in LibreOffice. Year is an integer between 1583 and 9957 or between 0 and 99. (first link) Year is a non-negative integer value or a reference to a cell containing that value, which is the year to be used. Values in the range 0 to 99 are converted to four-digit years in accordance with the Year (Two Digits) setting on the Tools ▸ Options ▸ LibreOffice ▸ General dialog. (second link) Please take a look on the help for DATE() https://help.libreoffice.org/7.6/en-US/text/scalc/01/func_date.html?DbPAR=CALC#bm_id3155511 some more explanations on: https://wiki.documentfoundation.org/Documentation/Calc_Functions/DATE There is not a bug.
This is very disappointing. Both Excel and Google Sheets can decipher this w/o error. This is a major compatibility issue. In this regard, it could be considered a bug. I don't know how to fix it to make LibreOffice happy (then would it work in Excel??).
This is LibreOffice being more strict about some calculations. Sure you can, in D10: =IF(ROUND(C10;4)=0;0;DATE(MOD(IF(MONTH(A9)>=12;(YEAR(A9)-1900)+1;(YEAR(A9)-1900));100);IF(MONTH(A9)=12;1;MONTH(A9)+1);DAY(A9))) It is using the MOD() function to get the rest from 100, the year with two digits. But in any case inside formulas four digits should be used, to avoid problems. =IF(ROUND(C10;4)=0;0;DATE(IF(MONTH(A9)>=12;YEAR(A9)+1;YEAR(A9));IF(MONTH(A9)=12;1;MONTH(A9)+1);DAY(A9))) which makes the formula even more simple.
And if I'm not wrong what you want, it's as simple as: =EDATE(A9;1)
Indeed, EDATE works nicely. Thank you.