Ask Your Question
0

Calculate age according to month/date

asked 2018-04-08 20:18:28 +0100

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

I have a column which is defined as MM/AAAA and contains birth date, and another which should show the age, calculating the difference between birth date and current date.

I would like to show age in years.

For example, if birth date is 05/2011 and date now is 04/2018, age is 6.
A month later age will be 7.

I tried to use DATEDIF but could not manage to get the expected result. How can I do it?

edit retag flag offensive close merge delete

Comments

I tried to use DATEDIF but could not manage to get the expected result.

Please always write what exactly did you try (what exact formula(s) in this case), to enable others to see what's the problem actually. Because if you have proper dates (not texts), then the formula =DATEDIF(A1;A2;"y") works for me.

Mike Kaganski gravatar imageMike Kaganski ( 2018-04-08 20:28:19 +0100 )edit

My problem is that I have only one cell as reference - the other date is current date. =DATEDIF(C1;NOW() ;"y") raises #VALUE!

Guy A. gravatar imageGuy A. ( 2018-04-08 21:39:46 +0100 )edit

And =DATEDIF(C6;NOW('mm/yyyy') ;"y") raises Err:508

Guy A. gravatar imageGuy A. ( 2018-04-08 21:40:46 +0100 )edit

Is C6 a proper date? Does it show you the date in the "sum" statusbar area? or is it a string actually?

Mike Kaganski gravatar imageMike Kaganski ( 2018-04-08 22:13:58 +0100 )edit

As I said, it is a date in format MM/AAAA (I have only month and year of birth), for example 05/2009

Guy A. gravatar imageGuy A. ( 2018-04-08 22:55:12 +0100 )edit

What you said didn't explain much. It could be a proper date (with cell formatting only showing the said date parts; btw, you use some localized characters for year that is usually shown as YYYY); or it could be the string looking like a date, but having no meaning as date (which seems to be your case). If it's just a month+year, then you can as well use the first day of a month and setup the display format. Then it will work.

Mike Kaganski gravatar imageMike Kaganski ( 2018-04-08 23:05:41 +0100 )edit

If you only have month/year and DATEDIF() gives a #VALUE! error, then that likely are not proper dates but strings instead. To calculate with dates you need dates instead of strings. So enter the birth date including the day of birth.

erAck gravatar imageerAck ( 2018-04-09 12:09:21 +0100 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2018-04-09 16:33:28 +0100

SteveB33 gravatar image

updated 2018-04-09 16:52:48 +0100

From what you've said I gather you simply want to calculate someones age given their birth date and the present date? then the formula is simply:

=DATEDIF(A1,A2,"y")

Note that the date format doesn't matter if either or birth date and present date are in the date format you specified "MM/YY" or "DD/MM/YYYY" as LibreOffice Calc always stores the full date. See attached image.

I created this formula in Excel so I know it works in both Excel and LibreOffice, to calculate how long I had worked in my last job to the present date and I needed to also calculate the months and the days. As you will see in the attached image. For demonstration purposes I have modified the formats of the start date and end dates, the start date is in the format DD/MM/YYYY and the end date MM/YY. Note that my formula takes into account if there are 0 Years, or only 1 Year, or more than one year, so it reads 0 Years, or 1 Year or 2 Years as the case may be.

So to calculate the years, months and days individually as per my example:

Years: =DATEDIF(B3,B6,"y")&IF(DATEDIF(B3,B6,"y")<>1," Years"," Year")

Months: =DATEDIF(B3,B6,"ym")&IF(DATEDIF(B3,B6,"ym")<>1," Months"," Month")

Days: =DATEDIF(B3,B6,"md")&IF(DATEDIF(B3,B6,"md")<>1," Days"," Day")

where "ym" restricts the result to months in the year, ignoring the years and likewise, "md" restricts the result to days in the month, otherwise using "m" or "d" it would literally return the total number of months and days elapsed between the 2 dates and in this case it would be undesired!

Or to concatenate the years, months and days into a one shot formula, simply separate each formula with and ampersand as shown below: =DATEDIF(B3,B6,"y")&IF(DATEDIF(B3,B6,"y")<>1," Years"," Year") & ", " &DATEDIF(B3,B6,"ym")&IF(DATEDIF(B3,B6,"ym")<>1," Months"," Month") &" and " & DATEDIF(B3,B6,"md")&IF(DATEDIF(B3,B6,"md")<>1," Days"," Day")

I trust that should answer your question and also help anyone else who is looking for a formula to calculate how someones age, or precisely how long someone has worked in their job, including months and days or whatever the case may be.

image description

Also note that it doesn't matter to LibreOffice Calc, if either the start date or the present date is just a date DD/MM/YY or calculated, say with:

=TODAY()

or

=A1+14

since the result will still be a date!

edit flag offensive delete link more

Comments

I set A11 cell to have the function =TODAY(), then C2 to have the value 06/2011 (this cell format is Date - MM/AAAA). In D6 I use the form =DATEDIF(C1;A11;"y"), but now I get #VALUE!. Any idea?

Guy A. gravatar imageGuy A. ( 2018-04-17 12:42:53 +0100 )edit

D6 should read:

=DATEDIF(C2,A11, "y")

Error 1: Your start/end dates are in A11 and C2 and youve put C1 which must be empty and produces the #VALUE! error Error 2: Semicolon between start date and end date where it must be a comma Error 3: Semicolon between arguments end_date and date_unit, where it must be a comma.

Note the DATEDIF function syntax is

DATEDIF(start_date,end_date, date_unit)

and start_date must be before end_date so A11 must be the start date and C2 must be the end date

SteveB33 gravatar imageSteveB33 ( 2018-04-18 02:01:29 +0100 )edit

Of course, stupid mistake :-) However, I have just fixed it to be =DATEDIF(A11,C3,"y"), and now I get Err:501. And idea?

Guy A. gravatar imageGuy A. ( 2018-04-19 12:33:18 +0100 )edit

Sorry for late reply, been really busy. I guess you would have figured it out by now but if not Err 501 usually indicates an invalid character somewhere and if its not a typo you have a comma after the closing bracket? see a complete list of error codes on the wiki @ https://wiki.openoffice.org/wiki/Calc...

SteveB33 gravatar imageSteveB33 ( 2018-05-10 00:28:54 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-04-08 20:18:28 +0100

Seen: 625 times

Last updated: Apr 09 '18