datedif function in Calc?
datedif function in Calc?
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.
Is there such a function in Calc as I haven't been able to find it.
Last edited by Skompy on Thu May 02, 2019 4:34 pm, edited 1 time in total.
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: datediff function in Calc?
A search of the forum would've shown you 60+ topics for DATEDIF.
search.php?keywords=datedif
search.php?keywords=datedif
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Re: datediff function in Calc?
-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.
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.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: datediff function in Calc?
I did do a search before posting but nothing relevant came up. Maybe I didn't type something correctly?RusselB wrote:A search of the forum would've shown you 60+ topics for DATEDIF.
search.php?keywords=datedif
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: datediff function in Calc?
Here is the video. He uses concatenation to achieve the 3 date elements in a single cell.Lupp wrote:Who did tell you Excel would return three numbers in one 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?
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: datedif function in Calc?
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.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: datedif function in Calc?
I have version 4.16 (just updated my profile).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.
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?
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: datedif function in Calc?
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.
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.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: datedif function in Calc?
Your suggested formula results in a date in the target cell.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.
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.
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: datedif function in Calc?
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.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: datedif function in Calc?
I did do what you suggested. The target cell did not show a number as you say.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.
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.
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: datedif function in Calc?
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)
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)
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: datedif function in Calc?
I need years, months, days in one cell. Yes, leap years are important.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)
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.
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: datedif function in Calc?
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.
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.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Re: datedif function in Calc?
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.
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.
Last edited by Skompy on Thu May 02, 2019 8:02 pm, edited 1 time in total.
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: datedif function in Calc?
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.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: datedif function in Calc?
I am not sure what you mean. The dates are in columns in my spreadsheet as direct entries.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.
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
-
- Volunteer
- Posts: 1549
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: datedif function in Calc?
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.without knowing if Libreoffice has handled leap years or not
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.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: datedif function in Calc?
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
Briefly tested, but no guarantees that some date combination will come back with the wrong information.
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"
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Re: datedif function in Calc?
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.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 B1Briefly tested, but no guarantees that some date combination will come back with the wrong information.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"
In addition, I also have 1 anomaly;
A1 = 28 Mar 2000 A2 = 19 Jan 2001 results of formula = 0y -3m 22d
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: datedif function in Calc?
Thanks for the additional information. I'll see what I can come up with.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.