# Calculating age on Excel

Discussion in 'Business Applications' started by joflaherty, Sep 1, 2004.

Not open for further replies.

Joined:
Mar 14, 2001
Messages:
85
I have set up an Excel spreadsheet that contains peoples dates of birth (eg cell E2) with the cells formated as date entry.
I have also entered a formula to calculate each person's age in years and months on today's date using the DATEIF and NOW commands. It seems to work perfectly:

=DATEDIF(E2,NOW(),"y") & "y " & DATEDIF(E2,NOW(),"ym") & "m "

However, when I sort the individuals AGE, Excel ranks 11 before 3.
How can I alter my formula and/or cells to create the months as 03 to compensate for this?

2. ### Rollin_Again

Joined:
Sep 4, 2003
Messages:
4,912
You are going to find it difficult to truly sort your data using your current method. Since you are concatenating the letter "Y" and "M" to your year and month calculations, you are forcing the cell to have a text format instead of a true number. Its not a good idea to sort numbers that are stored as text but if you insist, the only way to deal with this is to add "IF" logic to evaluate the length of the number returned by each of the DATEDIF functions and add a zero to it if it is less than 2.

The formula would be similar to the following

=IF(LEN(DATEDIF(E2,NOW(),"y"))<2,"0"&DATEDIF(E2,NOW(),"y")&" y ",DATEDIF(E2,NOW(),"y")& " y ") & IF(LEN(DATEDIF(E2,NOW(),"ym"))<2,"0"&DATEDIF(E2,NOW(),"ym")&" y ",DATEDIF(E2,NOW(),"ym")& " y ")

Also, by placing both the years and months in the same cell you will only be able to sort on the first number (year) which is being treated as text. If you store the year in one column and the month in the other you could sort by both criteria.

Your best bet is to use two seperate columns formatted as true numbers to hold each of the numbers. The first column (year) would have its own DATEDIF formula
DATEDIF(E2,NOW(),"y") and would have a custom number format of ## "Y"

The second column would have its own DATEDIF formula DATEDIF(E2,NOW(),"ym") and would have a custom number format of ## "M".

Rollin

As Seen On