How to calculate age based on Birthdate field

Hello, does anyone know Sugar Logic formula to calculate age based on a Birthdate date field.

Thank you

  • Hi Jean,

    Create a field for the Age with integer data type and make it a calculated field through the studio.

    By using the below formula you can calculate the Age.

    floor(divide(abs(daysUntil($birthdate)),365))

    Hope this helps you and you are looking for the same.

    Thanks,

    Poojitha K

  • Thanks it's working :)

  • Keep in mind that calculated fields are re-calculated only when you save the record, or by manually triggering the "Recalculate Values" in the List View Action Menu on a set of selected records, so if you have a record that has not been updated for a while the age will be wrong.

    To keep the field up to date you have a few options:

    • A Scheduler that updates  the field on all your records on a regular basis, could run once a month, for example, and check all birthdays that month to recalculate and update their age. This will allow you to store the age and use it in Reports.
    • A non-db field of a custom type where the value is calculated in the field's controller when the field is displayed
    • A non-db field that is calculated in the after-retrieve logic hook (note that this will NOT populate the age when you are creating a new record, because the after-retrieve does not trigger when displaying the results of creating a brand new record).

    I suppose you could make the last two DB fields that recalculate when the record is opened, but I think that would mean a LOT of unnecessary saves to your DB, and trigger the update of your Last Modified and Modified By every time one simply opens a record...

    All in all, I would recommend against adding such a field. Just a personal preference.

    FrancescaS

  • This is only an approximate figure. E.g. according to the formula someone who was born on March 3rd, 1958 is already 63 years old on February15th, 2021 (two weeks too early). If you want it exactly you have to take the leap years into account.

    But still a damn good formula. :-)

    PS: I think the full formula could be:

    floor(divide(subtract(abs(daysUntil($birthdate)),floor(divide(subtract(year(today()),year($birthdate)),4))),365))

    Harald Kuske
    Principal Solution Architect – Professional Services, EMEA
    hkuske@sugarcrm.com
    SugarCRM Deutschland GmbH

  • thanks for your alternate version, certainly shorter than the one mentioned here:

    https://sugarclub.sugarcrm.com/explore/enterprise-professional/f/enterprise-professional-questions/3155/reference-calculated-field-formulas/11383 

    add(subtract(number(subStr(toString(today()),12,4)),number(subStr(toString($birthdate),6,4))),ifElse(greaterThan(monthofyear($birthdate),monthofyear(today())),-1,ifElse(greaterThan(monthofyear(today()),monthofyear($birthdate)),0,ifElse(greaterThan(subtract(number(subStr(toString(today()),5,2)),number(subStr(toString($birthdate),3,2))),-1),0,-1))))

    .

    CRM Business Consultant

  • Haha, I did not see that other formula...

    In Sugar you always have two possible solutions.... 

    Harald Kuske
    Principal Solution Architect – Professional Services, EMEA
    hkuske@sugarcrm.com
    SugarCRM Deutschland GmbH

  • Actually, looking into this a little more, can shorten it and be similar precision (passes your test) with just this version?
    floor(divide(abs(daysUntil($birthdate)),365.242))

    .

    CRM Business Consultant

  • Hello 

    I wonder how it is supposed Sugar Logic to recalculate each all contact's age on a daily basis... That should be piece of work for a scheduled job, thought

    Have you set your mind regarding the approach to use? 

    Best Regards,
    Dmytro Chupylka

    integroscrm.com
    We make work in Sugar CRM system faster, more convenient and efficient

  • you are a genius!

    Harald Kuske
    Principal Solution Architect – Professional Services, EMEA
    hkuske@sugarcrm.com
    SugarCRM Deutschland GmbH