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.

(solved) Excel: How to Calculate Age w/No Decimals

Discussion in 'Business Applications' started by bccw, Dec 4, 2001.

Thread Status:
Not open for further replies.
Advertisement
  1. bccw

    bccw Thread Starter

    Joined:
    Dec 4, 2001
    Messages:
    3
    How do I calculate age from a date of birth in excel? The output must be in the form of a whole number from which I can calculate mean age etc..
     
  2. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Date of birth in cell A1


    =now()-a1 into the cell where you want the age to appear. This cell must be formatted as a number with 0 decimals, and NOT as a date.
     
  3. jbcalg

    jbcalg

    Joined:
    Oct 29, 2001
    Messages:
    2,056
    i had a more complicated version, using today, year functions, not a one stepper by any means (4 steps really)

    just a comment: given the usual time lag with demographic data, you might want to consider using a constant for your current year instead of a now or today function depending on the time period your stats are covering.

    ie
    mean age in 2000 using 2000 data would be wrong if you used now/today functions in your calculations, any more wrong in another month when your PC's date switches to 2002.
     
  4. bccw

    bccw Thread Starter

    Joined:
    Dec 4, 2001
    Messages:
    3
    Thank you 'Dreamboat' but unfortunately I couldn't get that to work (I've never encountered people that old!). Jbcalg - details of your four steps would be appreciated.
     
  5. jbcalg

    jbcalg

    Joined:
    Oct 29, 2001
    Messages:
    2,056
    i assumed you were working with a mm/dd/yy date format

    first convert to mm/dd/yy to year
    find today's date (see comments in previous post)
    convert to 4 digit year
    subtract

    here's the orig:
    here's a complicated way LOL

    dob in col A
    convert dob to number in col B using YEAR function
    today's date function in col C using TODAY
    convert current date to year in col D using YEAR
    [edit] subtract B from D in col E

    you can do calculations with col E but it's formula, not a number/integer,
    so you might want to - copy/paste special, value - col E to get a value to use in your calculations

    there's likely easier ways to do it, and i'd probably throw all this into a lookup type sheet, then just have the final age integer to work with on a main cal sheet

    here's a pic of the calcs and the formulas - formulas apply to row 3, copy down for subsequent rows (could probably use a $ ref for the converted current year)
     

    Attached Files:

    • age.jpg
      age.jpg
      File size:
      24.4 KB
      Views:
      100
  6. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    ROFL!

    Sorry!!

    That's =(now()-a1)/365.25


    (date calcs always calc the number of days, you wanted years)
     
  7. jbcalg

    jbcalg

    Joined:
    Oct 29, 2001
    Messages:
    2,056
    365.25 (approx) actually in the julian calendar
     
  8. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Isn't that what I put?

    :D
    ;)
    :D

    (LOL, thanks JB, you're perfectly right)
     
  9. bccw

    bccw Thread Starter

    Joined:
    Dec 4, 2001
    Messages:
    3
    Great, sorted. Many thanks both.
     
  10. jbcalg

    jbcalg

    Joined:
    Oct 29, 2001
    Messages:
    2,056
    bet you mourned the passing of invisible ink!!!
    :)
     
  11. Sponsor

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/60669

  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