+ Reply to Thread
Results 1 to 8 of 8

Excel formulas calculation age

  1. #1
    Registered User
    Join Date
    07-20-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    3

    Excel formulas calculation age

    I need help with a formula to calculate age.
    I have separated the dd/mm/yyyy (C1/D1/E1) into 3 different columns.
    Is there a way for me to still calculate age?

    Thanks

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    Re: Excel formulas calculation age

    try this in F1:

    =YEAR(TODAY())-E1

    or if you need only compared to 2009. just write: =2009-E1

  3. #3
    Registered User
    Join Date
    07-20-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Excel formulas calculation age

    Is it possible to take the month and date into consideration (within the formula) as well?

  4. #4
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Excel formulas calculation age

    If your values in C1:D1 are simply numeric values (not real Excel dates), then
    you will need to join the values and use the TEXT function to return a real date.


    Adjust this formula for your cell references and date format:

    =(TODAY()-TEXT(B1&"/"&A1&"/"&C1,"MM/DD/YYY"))/365

  5. #5
    Registered User
    Join Date
    07-20-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Excel formulas calculation age

    "you will need to join the values and use the TEXT function to return a real date."
    can you please explain this further.

    I changed the format of the column into text, and tried to put in the formula, but is still only taking into consideration the year solely.

  6. #6
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    Re: Excel formulas calculation age

    Try this (although i think it's not best solution):

    =IF(DATE(YEAR(TODAY());D1;C1)>TODAY();YEAR(TODAY())-E1-1;YEAR(TODAY())-E1)

    Also, it's not good to use TODAY() function so many times because it's volatile function and it will slow down your excel workbook.
    It's better to put in one cell =TODAY() (e.g. A1) and then refer to that cell:

    =IF(DATE(YEAR($A$1;D1;C1)>$A$1;YEAR($A$1)-E1-1;YEAR($A$1)-E1)

  7. #7
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Excel formulas calculation age

    "you will need to join the values and use the TEXT function to return a real date."
    can you please explain this further.

    I changed the format of the column into text, and tried to put in the formula, but is still only taking into consideration the year solely.
    You don't need to format the column into text. You only need to join together the values in C1:E1 into a valid text string that Excel can recognize as a date. The TEXT function serves this purpose.

    See attached for example.
    Attached Files Attached Files

  8. #8
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    Re: Excel formulas calculation age

    maybe this...

    =2009-E1-MAX(0;MOD(MONTH(TODAY());D1)-6)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1