Libreoffice Calc not computing difference between 2 dates

[4joeyirosh1]

Hi!I have installed Debian 10 Buster 64 bit on my laptop and utilize the latest version of LibreOffice suite which includes Libreoffice Calc version 6.1.5.2 Build ID: 1:6.1.5-3+deb10u4.

I have several excel sheets with thousands of records with dates and I was hoping to compute the date differences between 2 dates.For instance I have date 27/09/19 ie September 27th,2019 on cell A2 and 03/10/19 October 3rd,2019 on cell A3.I have right clicked on both cells and from format cells the category is Date,Format is 31/12/99 and Format Code is DD/MM/YY.

In this example I assumed that it would be easy to compute difference in dates between the dates above by placing the simple formula =A3-A2 but it gives me output #VALUE!.Even when I try the option of =DATEDIF(A3,A2,"D") I get same issue ie #VALUE!

Please help in resolving this.Thanks in advance

[v&n]

..I have date 27/09/19 ie September 27th,2019 on cell A2 and 03/10/19 October 3rd,2019 on cell A3.I have right clicked on both cells and from format cells the category is Date,Format is 31/12/99 and Format Code is DD/MM/YY.

Hi.
Is it possible that the dates have changed to text values somehow? That is, preceded by an apostrophe ('), like '03/10/19 instead of 03/10/19?
It may happen, for example, if the cells' format was initially set to (or recognized as) 'General', and changed to 'Date' later. Thus the numbers inside them now being treated as 'text' placed in cells formatted as number cells.

If so, you may have to find-replace those apostrophes, unless someone can suggest a better way : https://www.ryananddebi.com/2009/11/29/ ... fice-calc/

[PhilGil]

There may be something weird about the way Excel formats date that's throwing Calc off. Maybe try the DATEVALUE function...

Code: Select all

=DATEVALUE(A3)-DATEVALUE(A2)