[Solved] Calculate a persons age from birthdate

[dgsarnow]

I am trying to create a formula to calculate a person age based on their brithdate. I did some digging on-line and found this one: =YEARS(A1;NOW();0) , but when I run it on a person with a birthdate of 12/11/90 is coming up with an age of 108. CAn someone help me get this sorted?

Cheers,

Dave

[Villeroy]

Your "12/11/90" is a text value (and ambiguous). Your text value evaluates to zero which is 1899-12-30 in date context, thus 108 years.
I get 17 years for valid dates 1990-11-12 and 1900-12-11.
=YEARS(VALUE(A1);NOW();0) may work for you.

[dgsarnow]

I have 27/5/91 in the date of birth field and the cell is formatted to date. So I am not sure why it does not use the value of the date. When I use =YEARS(VALUE(A1);NOW();0) I get the result 16/01/00.

Do I need to process the date differently? Or format the cell differently?

Thanks for the help.

[Villeroy]

No number format will ever change any text values. Number formats format numbers. A text value "12/13/90" is a sequence of 8 characters like a phone number, a postal code, a part number or any other sequence of characters that happen to contain digits. The difference between text and number is fundamental in spreadsheet formulas as in any other computer language or even in text processors like Writer.

[Villeroy]

When I use =YEARS(VALUE(A1);NOW();0) I get the result 16/01/00.

which is the resulting number of years, formatted as date. Remove the number format from that number.
The 16th January 1900 is number 18 formatted as date (day number 18 since day zero 1899-12-30)

[dgsarnow]

Ok. I have a spreadsheet with the dates entered as 29/5/91. The formula you generously provided did not work. What can I do to process these cells so I can get the formula to work. I am grateful for your time and expertise.

Cheers,

Dave

[Villeroy]

I guess, you formatted your spreadsheet to death.
Select the date cells (or entire columns of)
Apply default style: Hit F11 and double-click cell style "Default"
Remove all hard formatting (Ctrl+Shift+Space)
Hit Ctrl+F8 (View>Highlight values). If your dates are text they are black, numeric values appear with blue font.

"12/13/90" is December the 13th in US. If your system is not US English you should apply locale "English(US)" to either one of the date-cells' number format locale, the document's default cell style or globally Tools>Options...Languages>Locale.

Convert all text to number (if possible):
Select the cells in question
Edit>Find&Replace (Ctrl+F)
[More Options]
[X]Current Selection
[X]Regular Expression

Search: .+
Replace: &
[Replace All]
Basically, this retypes everything into unformatted cells hopefully using a US locale now.

Ctrl+F8 should show blue dates now. Now you can change the number format and/or the language or those numbers.

[ashapiro]

I want to calculate age when the birth day and month are unknown. I have been using

=YEARS(A1; TODAY(); 0)

which works when I know the exact birth date. By only inputting the year of birth, how do I calculate a person's age today that is accurate +/- 1 year?

[acknak]

Maybe something like

=YEARS(DATE(A1;1;1); TODAY(); 0)

[Villeroy]

By only inputting the year of birth, how do I calculate a person's age today that is accurate +/- 1 year?

=YEAR(TODAY())-A1

with A1 having a 4-digit number, say 1953, and TODAY being some day in 2013, YEAR(TODAY()) gives 2013 and 2013-1953 => 60

[Jinnytx]

Hi. I'm not a programmer or anything close to that, but I used this way to get the age.

For example,
A1 : 1953-12-29 (Birthday)
B1 : =TODAY() <--2015-02-04
C1 : =B1-A1 <----- Came out 61

Then, I hid B1 from my spreadsheet (for my report).

So simple, so it works for me.
I hope this can help someone.
Thanks.

[Villeroy]

Hi. I'm not a programmer or anything close to that, but I used this way to get the age.

For example,
A1 : 1953-12-29 (Birthday)
B1 : =TODAY() <--2015-02-04
C1 : =B1-A1 <----- Came out 61

Then, I hid B1 from my spreadsheet (for my report).

So simple, so it works for me.
I hope this can help someone.
Thanks.

Plain wrong.
=TODAY()-DATE(1953;12;29) ==> 22318 (days)

age-calculator spreadsheet

[ZARA]

Notification to this point, in the event that you have to calculate a man's age from their introduction to the birth date, you can do as such with the YEARFRAC, INT, and TODAY functions. This formula utilizes the TODAY function; it will keep on figuring the right age later on too.

Age Formula

=INT(YEARFRAC(D4,TODAY(),1))