RusselB wrote:A search of the forum would've shown you 60+ topics for DATEDIF.
https://forum.openoffice.org/en/forum/search.php?keywords=datedif
Lupp wrote:Who did tell you Excel would return three numbers in one cell?
RoryOF wrote: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.
RoryOF wrote: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.
RoryOF wrote: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.
RoryOF wrote: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)
RoryOF wrote: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.
without knowing if Libreoffice has handled leap years or not
=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"
RusselB wrote: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 Expand viewCollapse view
=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.
Users browsing this forum: No registered users and 29 guests