How to convert an Excel formula to Calc

[debbys]

Hi

I have tried to figure it out myself and failed miserably.

I have the following formula from excel imported on a file converted to open office:

=DATEDIF(C5,TODAY(),"Y")

I literally want to know age in years, not years and months. I have popped = and selected YEARS from functions which gives me (Start_date; End_date: Type) and have tried entering =YEARS(26/4/13; now; 1) and =YEARS(26-Apr-13; now; 1) but neither work I am guessing I am guessing I am missing something simple but cannot figure what

Please enlighten me

Thank you

[RoryOF]

Try =(Today()-Cell_holding_StartDate)/365.25

There are more accurate methods, but that might be sufficient for you

[Villeroy]

In any spreadsheet 26/4/13 means 26 divided by 4 divided by 13.
A clean way to specify a day number: DATE(2013;4;13) returns the day number of the date that is specified by year;month;day. Notice that OpenOffice always uses semicolon as argument separator.
TODAY() returns today's day number.
=YEARS(DATE(2013;4;13);TODAY();0) returns the number of full years between the 2 day numbers as in age calculations.
Instead of DATE(2013;4;13) you may use the ISO string of a date enclosed in double-quotes: "2013-4-14"

[John_Ha]

If you want to do it properly you need

1. An Excel Manual so you know exactly what the Excel formula does.
2. A Calc Guide (manual) so you can replicate exactly what the formula does with the functions available in Calc.

A list of Excel functions can be found at Excel functions (alphabetical)

You can download the Calc Guide from here. Functions are listed in Appendix B.

[RoryOF]

In my example Cell_holding_StartDate was formatted as Date, DD/MM/YY.

[Zizi64]

The LibreOffice Calc has DATEDIF() function:

[John_Ha]

The Excel function calculates the years between a start date and an end date.

The closest Calc function is YEARS which purports to do the same. However I could not get it to work using the definition as in the manual with dates like "25-12-2016".

However the example given in the YEARS function in the Calc wiki - found by using Help - does work. See Documentation/How Tos/Calc: YEARS function

Code: Select all

=YEARS("2009-04-03"; "2011-11-17"; 0)

gives the value 2. Note the spaces, quotes, the date format used and the " 0 " which selects years where

If mode is 0, YEARS returns the number of whole years between startdate and enddate, day/month to day/month.

If mode is 1, YEARS identifies the year that startdate and enddate each lie in, and returns the difference between those years. In other words it returns YEAR(enddate) - YEAR(startdate)

You will appreciate the necessity for understanding exactly what the Excel and Calc functions do.

years.ods

(7.76 KiB) Downloaded 85 times

Showing that a problem has been solved helps others searching so, if your problem is now solved, please view your first post in this thread and click the Edit button (top right in the post) and add [Solved] in front of the subject.

[Villeroy]

Since the topic is about "Excel formulas", YEARS is not Excel compatible.
A compatible formula would be:

Code: Select all

YEAR(B1)-YEAR(A1)-(MONTH(B1)<MONTH(A1))-AND(MONTH(B1)=MONTH(A1);DAY(B1)<DAY(A1))

which calculates the difference between year numbers and subtracts one year if the month is lower or if the month is the same but day number is lower.
or this one

Code: Select all

YEAR(B1)-YEAR(A1)-(DATE(YEAR(B1);MONTH(A1);DAY(A1))<B1);

which calculates the difference between year numbers and subtracts 1 if the A1 date in the year of B1 is smaller than B1.

The Calc component in https://libreoffice.org/ has a DATEDIF function like Excel and with English locale settings it can deal with commas as list separators.

[John_Ha]

Since the topic is about "Excel formulas", YEARS is not Excel compatible.

I read the post as "A file created with Excel has been saved as a .ods file. A Calc equivalent to an Excel function is required. The one I tried using YEARS does not work" and gave a Calc equivalent by getting YEARS to work.

Of course, if the .ods file is to be edited by both Excel and by Calc then, as you say, an Excel and Calc compatible formula such as yours will be required.

[Villeroy]

Or if you save it as xls and re-open with Calc. People do this kind of mistakes.