datedif function in Calc?

[Skompy]

I have watched a video about Excel having a datedif function, which shows in a single cell, the number of years, months and days between 2 dates.

Is there such a function in Calc as I haven't been able to find it.

[RusselB]

A search of the forum would've shown you 60+ topics for DATEDIF.
search.php?keywords=datedif

[Lupp]

-1- You shouldn't expect Excel to use a reasonable abbreviation as you did in the subject.
The function is named DATEDIF() with a single "F".
-2- Who did tell you Excel would return three numbers in one cell?
See https://support.office.com/en-us/articl ... 32a451b35c
-3- Recent versions of LibreOffice have DATEDIF implemented. And also TEXTJOINN (a little joke). See attached image.

[Skompy]

A search of the forum would've shown you 60+ topics for DATEDIF.
search.php?keywords=datedif

I did do a search before posting but nothing relevant came up. Maybe I didn't type something correctly?

[Skompy]

Who did tell you Excel would return three numbers in one cell?

Here is the video. He uses concatenation to achieve the 3 date elements in a single cell.
https://www.youtube.com/watch?v=Q3D5h2mwfvE

I don't want to use actual dates in the formula as there are thousands of dates in my spreadsheet. And I tried entering cell refs in the DATEDIF formula, in the following syntaxes

=DATEDIF(a1,a2,"Y")
=DATEDIF(a1;a2,"Y")

both resuls in Err:508. Is there a way to use cell references instead of actual dates?

[RoryOF]

Your Forum .sig tells that you are using AOO 4.1.5; there is no DateDif implemented in that version. You will need to write a macro or enter a formula to achieve the desired object. By the way, the separator in OO is a semicolon, not a comma.

[Skompy]

Your Forum .sig tells that you are using AOO 4.1.5; there is no DateDif implemented in that version. You will need to write a macro or enter a formula to achieve the desired object. By the way, the separator in OO is a semicolon, not a comma.

I have version 4.16 (just updated my profile).

So is there another way to achieve what I am looking for?
I do plan to create a database for my data and so perhaps I should wait until that time before introducing this element?

[RoryOF]

As far as I know OO 4.1.6 does not have DATEDIF implemented. Write a macro or use a formula.

Enter start date in A1, end date in B1, format the target cell as Date; in it place =Days(B1;A1) and see what you get.

[Skompy]

As far as I know OO 4.1.6 does not have DATEDIF implemented. Write a macro or use a formula.

Enter start date in A1, end date in B1, format the target cell as Date; in it place =Days(B1;A1) and see what you get.

Your suggested formula results in a date in the target cell.
I do use the =DAYS function elsewhere but it doesn't provide what I need. The DATEDIF function does provide this, if it was available, along with concatenation as in the above video. I don't know about macros.

The reason I need this functionality is so that I can see, at a glance, what the time difference is, in an understandable way, across thousands of rows or when I get to making reports of some kind. Seeing 4911 days is not useful to me.

[RoryOF]

If you did exactly as I suggested you would have seen a pseudo date - 4911 would render as 11/06/13 (using DD/MM/YY formatting); with a little thought you could manipulate the results to obtain the exact display you require.

[Skompy]

If you did exactly as I suggested you would have seen a pseudo date - 4911 would render as 11/06/13 (using DD/MM/YY formatting); with a little thought you could manipulate the results to obtain the exact display you require.

I did do what you suggested. The target cell did not show a number as you say.
I don't know what to do afterwards since the target cell is formatted as a date.
If I could think of what to do myself I would not be asking for help.

[RoryOF]

If you change formatting of the cell so that you get 4911 days, how you break this back to years and months is simple spreadsheet arithmetic. Any online tutorial or introductory book on spreadsheets will give you that.

For example =INT(4911/365) will give you the number of years (ignoring leap years). Does the exact month count matter, based on calender months? If not, perhaps 30 day months will suffice, so =INT(4911/30)

[Skompy]

If you change formatting of the cell so that you get 4911 days, how you break this back to years and months is simple spreadsheet arithmetic. Any online tutorial or introductory book on spreadsheets will give you that.

For example =INT(4911/365) will give you the number of years (ignoring leap years). Does the exact month count matter, based on calender months? If not, perhaps 30 day months will suffice, so =INT(4911/30)

I need years, months, days in one cell. Yes, leap years are important.
I do not think I can get what I want in Calc, but I have managed to achieve it, as per the result in the YT video, using Libreoffice.

[RusselB]

OpenOffice Calc can do this, but with a lot of finagling and helper columns... eventually you might be able to get rid of the helper columns, but I don't think you'd be able to get rid of all of them.
As already noted, LibreOffice Calc does have the DATEDIF function. I haven't needed to use this function, so I don't know what display it gives.

[Skompy]

Although I have managed to get the result I was after (albeit without knowing if Libreoffice has handled leap years or not), my spreadsheet is very clunky in LibreOffice and moving around the spreadsheet is arduous, with the swirling beach ball making a regular appearance.

I was able to get a display of XY XM XD in each cell (the labels for each date component can be set to suit), so it does work. Here is the formula;

=DATEDIF(Q1,U1,"Y")&"y "&DATEDIF(Q1,U1,"YM")&"m "&DATEDIF(Q1,U1,"MD")&"d"


Perhaps though the formula across 575 rows which I tested it on (and which is only a subset of the total data) is causing the slow working of my spreadsheet.

Perhaps I should wait until I get the data into a database before attempting this component?

Thanks to all who offered help.

[RoryOF]

If this information is being derived from data, then best to use the original data in the database, and calculate the derived information in a report or with a filter.

[Skompy]

If this information is being derived from data, then best to use the original data in the database, and calculate the derived information in a report or with a filter.

I am not sure what you mean. The dates are in columns in my spreadsheet as direct entries.

[UnklDonald418]

without knowing if Libreoffice has handled leap years or not

Internally dates are actually stored as a floating point value, with the value to the left of the decimal point being the number of days since December 31, 1899. The date displayed is calculated from the stored value so yes, leap years are handled.
The number can also be negative, but there are some date anomalies if your dates are before December 31,1899 so be careful if that is the case.
The value to the right of the decimal point is where times are stored.

[RusselB]

If anyone is interested, I came up with the following formula that (seems to) return the information per the original request in OpenOffice Calc.
Note: Earliest date is in A1, Latest date is in B1

Code: Select all

=YEARS(A1;B1;1)-IF(MONTHS(A1;B1;1)-YEARS(A1;B1;1)*12-IF(DAY(B1)-DAY(A1)<0;1;0)<0;1;0)&"y "&MONTHS(A1;B1;1)-YEARS(A1;B1;1)*12-IF(DAY(B1)-DAY(A1)<0;1;0)&"m "&IF(DAY(B1)-DAY(A1)>=0;DAY(B1)-DAY(A1);DAYS(B1;DATE(YEAR(B1);MONTH(B1)-1;DAY(A1))))&" d"

Briefly tested, but no guarantees that some date combination will come back with the wrong information.

[Skompy]

If anyone is interested, I came up with the following formula that (seems to) return the information per the original request in OpenOffice Calc.
Note: Earliest date is in A1, Latest date is in B1

Code: Select all

=YEARS(A1;B1;1)-IF(MONTHS(A1;B1;1)-YEARS(A1;B1;1)*12-IF(DAY(B1)-DAY(A1)<0;1;0)<0;1;0)&"y "&MONTHS(A1;B1;1)-YEARS(A1;B1;1)*12-IF(DAY(B1)-DAY(A1)<0;1;0)&"m "&IF(DAY(B1)-DAY(A1)>=0;DAY(B1)-DAY(A1);DAYS(B1;DATE(YEAR(B1);MONTH(B1)-1;DAY(A1))))&" d"

Briefly tested, but no guarantees that some date combination will come back with the wrong information.

Thanks for that formula. I am finding however that the results are showing -1 instead of 0 for the month figure when the two dates are within the same month.

In addition, I also have 1 anomaly;

A1 = 28 Mar 2000 A2 = 19 Jan 2001 results of formula = 0y -3m 22d

[RusselB]

Thanks for the additional information. I'll see what I can come up with.