datedif function in Calc?

Discuss the spreadsheet application

datedif function in Calc?

Postby Skompy » Thu May 02, 2019 3:03 pm

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.
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.
Skompy
 
Posts: 98
Joined: Tue Feb 18, 2014 10:35 am

Re: datediff function in Calc?

Postby RusselB » Thu May 02, 2019 3:25 pm

A search of the forum would've shown you 60+ topics for DATEDIF.
https://forum.openoffice.org/en/forum/search.php?keywords=datedif
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
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.
User avatar
RusselB
Moderator
 
Posts: 5669
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: datediff function in Calc?

Postby Lupp » Thu May 02, 2019 4:01 pm

-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.
DATEDIF_TEXTJOIN_1.png
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2564
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: datediff function in Calc?

Postby Skompy » Thu May 02, 2019 4:18 pm

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


I did do a search before posting but nothing relevant came up. Maybe I didn't type something correctly?
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.
Skompy
 
Posts: 98
Joined: Tue Feb 18, 2014 10:35 am

Re: datediff function in Calc?

Postby Skompy » Thu May 02, 2019 4:34 pm

Lupp wrote: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?
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.
Skompy
 
Posts: 98
Joined: Tue Feb 18, 2014 10:35 am

Re: datedif function in Calc?

Postby RoryOF » Thu May 02, 2019 4:44 pm

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.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29885
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: datedif function in Calc?

Postby Skompy » Thu May 02, 2019 4:47 pm

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.


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?
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.
Skompy
 
Posts: 98
Joined: Tue Feb 18, 2014 10:35 am

Re: datedif function in Calc?

Postby RoryOF » Thu May 02, 2019 4:55 pm

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.
Apache OpenOffice 4.1.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29885
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: datedif function in Calc?

Postby Skompy » Thu May 02, 2019 5:32 pm

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.


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.
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.
Skompy
 
Posts: 98
Joined: Tue Feb 18, 2014 10:35 am

Re: datedif function in Calc?

Postby RoryOF » Thu May 02, 2019 5:38 pm

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.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29885
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: datedif function in Calc?

Postby Skompy » Thu May 02, 2019 5:55 pm

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 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.
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.
Skompy
 
Posts: 98
Joined: Tue Feb 18, 2014 10:35 am

Re: datedif function in Calc?

Postby RoryOF » Thu May 02, 2019 6:05 pm

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)
Apache OpenOffice 4.1.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29885
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: datedif function in Calc?

Postby Skompy » Thu May 02, 2019 6:59 pm

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 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.
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.
Skompy
 
Posts: 98
Joined: Tue Feb 18, 2014 10:35 am

Re: datedif function in Calc?

Postby RusselB » Thu May 02, 2019 7:50 pm

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.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
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.
User avatar
RusselB
Moderator
 
Posts: 5669
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: datedif function in Calc?

Postby Skompy » Thu May 02, 2019 7:56 pm

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.
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.
Skompy
 
Posts: 98
Joined: Tue Feb 18, 2014 10:35 am

Re: datedif function in Calc?

Postby RoryOF » Thu May 02, 2019 7:58 pm

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.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29885
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: datedif function in Calc?

Postby Skompy » Thu May 02, 2019 8:04 pm

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.


I am not sure what you mean. The dates are in columns in my spreadsheet as direct entries.
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.
Skompy
 
Posts: 98
Joined: Tue Feb 18, 2014 10:35 am

Re: datedif function in Calc?

Postby UnklDonald418 » Fri May 03, 2019 2:01 am

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.
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.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1257
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: datedif function in Calc?

Postby RusselB » Fri May 03, 2019 4:36 am

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.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
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.
User avatar
RusselB
Moderator
 
Posts: 5669
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: datedif function in Calc?

Postby Skompy » Fri May 03, 2019 3:56 pm

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.


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
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.
Skompy
 
Posts: 98
Joined: Tue Feb 18, 2014 10:35 am

Re: datedif function in Calc?

Postby RusselB » Fri May 03, 2019 8:08 pm

Thanks for the additional information. I'll see what I can come up with.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
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.
User avatar
RusselB
Moderator
 
Posts: 5669
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON


Return to Calc

Who is online

Users browsing this forum: Google [Bot], RusselB and 38 guests