How to Create a Salesforce Formula Field to Calculate Age

A company wants to know the exact age of its customers based on the Date of Birth stored in Salesforce.

Instead of calculating it manually every time, they want a formula field that will automatically show the customer’s age in years on the record.

In this article, we will learn how to use the Salesforce formula field to calculate the age. Moreover, we will go through the steps to display the age value in Salesforce.

Salesforce Formula Field to Calculate Age

For event management, financial planning, healthcare management, contact management, and other similar services, we require age verification. To keep track of age, we need to define an advanced formula for calculating age.

And to create the age calculation formula, Salesforce provides us with various built-in functions that help us calculate it. Let’s have a look at an advanced formula that calculates age in Salesforce.

Formula #1

FLOOR((TODAY() - Birthdate ) / 365.25)

In this formula, we calculate the age based on the date of birth, but make sure the year of the dob is a leap year. Here, we use the TODAY() function to get the current date, and then we subtract it from the date of birth.

Next, we divide the result by 365.25 as we assume that the year of the date of birth field is a leap year, and then, to round the result to the nearest integer, we use the FLOOR() function.

Formula #2

ROUND((TODAY() - Birthdate ) / 365, 0)

In this formula, we calculate the age based on the date of birth. Here, we use the TODAY() function to get the current date, and then we subtract it from the date of birth.

Next, we divide the result by 365, and then to round the result to the nearest integer, we use the ROUND() function.

Formula #3

TRUNC((TODAY() - Birthdate) / 365.2425)

In this formula, we determine the age based on the date of birth but make sure the year of the dob is a leap. Here, we use the TODAY() function to get the current date, and then we deduct it from the birthdate.

Since the year of the date of birth field is assumed to be a leap year, we next divide the result by 365.2425. Finally, we use the TRUNC() function to round the result to the nearest integer.

Formula #4

FLOOR((TODAY() - Birthdate) / 365)

In this formula, the age is determined using the birthdate. Here, we obtain the current date using the TODAY() function and then deduct it from the birthdate.

The value is then divided by 365, and then the FLOOR() function is used to round the result to the nearest integer.

Formula #5

YEAR(TODAY()) - YEAR( Birthdate ) - IF(MONTH(TODAY()) < MONTH(Birthdate) || (MONTH(TODAY()) = MONTH(Birthdate) && DAY(TODAY()) < DAY(Birthdate)), 1, 0)

In this formula, the year, month, and day components of the current date and the date of birth are extracted using the YEAR(), MONTH(), and DAY() functions.

We then calculate the number of years by subtracting the birth year from the current year. We also use the IF() function to check if the birth month and day have not yet passed in the current year; it adjusts the result by deducting 1.

Formula #6

CASE(MONTH(TODAY()) - MONTH( Birthdate ),
0,
IF(DAY(TODAY()) >= DAY(Birthdate), YEAR(TODAY()) - YEAR(Birthdate), YEAR(TODAY()) - YEAR(Birthdate) - 1),
IF(MONTH(TODAY()) < MONTH(Birthdate), YEAR(TODAY()) - YEAR(Birthdate) - 1, YEAR(TODAY()) - YEAR(Birthdate))
)

• Based on the month and day parts of the birthdate and the current date, this formula utilises the CASE() function.
• In the first case, we check if the birth month is less than the current month; we subtract the birth year from the current year to get the number of years.
• Next, we check if the birth month is equal to the current month, if it is then we check if the birthday has passed in the current month, if it is we adjust the number of years.
• After that, we check if the birth month is greater than the current month; if it is, we subtract 1 from the number of years.

We can use any of the above-defined formulas by creating a custom formula field in Salesforce. Let’s move to the next section and learn to create a formula field on the object where we want to calculate age.

Calculate Age Formula Field in Salesforce

Here are the steps to define the age formula field in Salesforce:

1. Click the Setup icon in the top right corner of the page to access Salesforce setup.
   • Click Object Manager from the menu’s left side.

2. Locate the item for which we want to calculate the age. In this example, I choose the Contact object from the list of objects.

3. Choose Field & Relationships. Next, click the New button located at the top right side of the page.

4. Select the radio button next to the Formula data type. To proceed, click on the Next button.

5. Enter the Field Label for the formula, and the Field Name will be automatically populated. Here, I set the Field Label to Age.
   • Now, check the radio button next to ‘Number’ to set it as the formula return type. Additionally, set the decimal places to zero.
   • Then, to proceed, click on the Next button.

6. In the advanced formula editor, enter the formula that calculates the person’s age. Here is the formula:

CASE(MONTH(TODAY()) - MONTH( Birthdate ),
0,
IF(DAY(TODAY()) >= DAY(Birthdate), YEAR(TODAY()) - YEAR(Birthdate), YEAR(TODAY()) - YEAR(Birthdate) - 1),
IF(MONTH(TODAY()) < MONTH(Birthdate), YEAR(TODAY()) - YEAR(Birthdate) - 1, YEAR(TODAY()) - YEAR(Birthdate))
)

Here, we use the IF and CASE functions with the TODAY, MONTH, DAY, and YEAR functions to calculate the number of years between the birthdate and the current date to determine the person’s age.

7. To verify whether we have written the correct formula, we need to click the Check Syntax button.
   • If you want to handle the empty field and also want to define the Description and Help Text, we can add it.
   • Then, click on Next.

8. Select the appropriate Field Level Security and make it visible. Here, I check the Visible checkbox as I want it to be visible to all.
   • Then, click on Next.

9. After that, select the Page Layout name for which the formula field is to be added.
   • Finally, click on the Save button to save the formula field.

10. We can use the formula field once it has been created. Let’s look at an example:
    • Create a contact with the Birth Date field by opening the Contacts item and saving it.
    • After that, click on the Details section, and here the Age field appears.

As a result, we now know how to use Salesforce Lightning’s advanced formula to calculate the age.

Conclusion

With this, we have learned how to use the Salesforce Formula Field to calculate the Age. Moreover, we have learned the procedure for determining the age of a person in Salesforce.

You may also like:

• Salesforce Due Date Formula
• Salesforce Last Activity Date Formula
• Salesforce Date Range Formula
• Salesforce Formula Field to Check Null Values for Dates

Bijay Kumar

I am Bijay Kumar, the founder of SalesforceFAQs.com. Having over 10 years of experience working in salesforce technologies for clients across the world (Canada, Australia, United States, United Kingdom, New Zealand, etc.). I am a certified salesforce administrator and expert with experience in developing salesforce applications and projects. My goal is to make it easy for people to learn and use salesforce technologies by providing simple and easy-to-understand solutions. Check out the complete profile on About us.