Calculating age on Excel

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

joflaherty

Thread Starter
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?
 
Joined
Sep 4, 2003
Messages
4,916
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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top