1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Calculating age on Excel

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

Thread Status:
Not open for further replies.
  1. joflaherty

    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?
     
  2. Rollin_Again

    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
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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/269041

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice