May 27, 2011

Calculating Age From Birth Date

It looks very simple but a bit complicated question, what if I need to calculate the age from birth date in formula field whose return type is text?


Answer for Calculating Age From Birth Date :


Just copy paste this in your formula field, it will provide you the exact age calculated from birth date.
IF(MONTH(TODAY())>MONTH(DOB_API),YEAR(TODAY())-YEAR(DOB_API),IF(AND(MONTH(TODAY())=MONTH(DOB_API),DAY(TODAY())>=DAY(DOB_API)),YEAR(TODAY())-YEAR(DOB_API),(YEAR(TODAY())-YEAR(DOB_API))-1))
First simply if the month of present year is greater than date of birth than age will be calculated as the difference between present year and year of birth.


Secondly if the first scenario fails two options comes in my mind, if the month of present year is same then is day of birth is same or not so if day of birth is greater than or equals to day of birth than age is calculated as we have done in the first scenario.


Thirdly if above both scenarios fails that means month of present year and month of birth date is same but day of birth is smaller than day of present day. So age is calculated as the difference between present year and year of birth -1


See I told you it looks simple but bit complicated.


Cheers

4 comments:

  1. I want to display age in formula field that is 20years 8months 21days.
    And how it will work,what is the formula here. and what datatype are use to in formula field

    ReplyDelete
  2. IF(ISNULL(DateofBirth__c),"",TEXT(FLOOR((TODAY()-DateofBirth__c)/365.2425)) & " year's " & TEXT(FLOOR(MOD((TODAY()-DateofBirth__c),365.2425)/30)) & " Month's " & Text(Floor(MOD(Floor(MOD((TODAY()-DateofBirth__c),365.2425)),30))) & " Day's ",
    NULL
    )

    ReplyDelete
  3. it is showing....Incorrect number of parameters for function 'IF()'. Expected 3, received 4 (Related field: Formula)

    ReplyDelete
  4. Thanks to the author. This is what i was looking for several hours. However! i have found completely automated Microsoft Excel Formula for Age calculation, here is the link to complete video tutorial. Excel True Age Calculator Magic Formula + Ready Worksheet Download

    ReplyDelete