Saturday, May 19, 2012

Calculate Age in Excel (2) - the age at the nearest birthday


Several functions in EXCEL can be used to determine the age such as, DATEDIF(), YEARFRAC().

Which one should be applied depends on the definition of the age.

The age is defined as age at nearest birthday.



·       Method 1,    DATEDIF(DATE1,DATE2,”m”) Combined with ROUND()

For example,

DOB (Date of Birth)                 5/20/1976

DOT (Date of Target)               11/20/2012

AGE at DOT                                                  37

In Cell B1, Type in 5/20/1976

In Cell B2, Type in 11/20/2012

In Cell B3, Formula= =ROUND(DATEDIF(B1,B2,"m")/12,0)

Thus, The Formula is ROUND(DATEDIF(DOB, DOT, ”m”)/12,0)



·       Method 2, YEARFRAC(DATE1,DATE2,0) Combined with INT()

In Cell B1, Type in 5/20/1976

In Cell B2, Type in 11/20/2012

In Cell B3, Formula= =INT(YEARFRAC(B1,B2,0)+0.5)

Thus, The Formula is INT(YEARFRAC(DOB, DOT, 0)+0.5)



© Copyright Exceltipsandkeys All Rights Reserved.

2 comments:

  1. Thanks for sharing the info.I believe that you recognize how to construct people pay attention to what you have to pronounce, particularly with a concern that’s so vital. I am pleased to suggest this blog. Please visit our site @ www.gofastek.com

    Sed

    ReplyDelete
  2. Love it! Very interesting topics, I hope the incoming comments and suggestion are equally positive. Thank you for sharing this information that is actually helpful.


    ufgop.org
    ufgop.org

    ReplyDelete