Salesforce Formula Field to Calculate Age

In this Salesforce tutorial, we will learn the Salesforce Formula Field to Calculate the Age. Additionally, we will go over how to use Salesforce Lightning and Salesforce Classic to calculate the age in a formula field.

I was working as HR Manager in an organization that uses Salesforce as a CRM tool. Here, I got the responsibility of tracking the age of employees. So, for this, I define an advanced formula that calculates age from Date of Birth in Salesforce.

If you want to learn the process to add a formula field that calculates the age, you have come to the right place. The following are the topics that we will cover in this salesforce tutorial:

  • Salesforce Formula Field to Calculate Age
  • Calculate Age Formula Field in Salesforce Lightning
  • Calculate Age Formula Field in Salesforce Classic

Salesforce Formula Field to Calculate Age

For event management, financial planning, healthcare management, contact management, etc, we need age. To keep track of age, we need to define the age calculation advance formula.

And to create the age calculation formula, Salesforce provides us with various built-in functions that help us to calculate it. Let’s have a look at some advance 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. 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, 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, 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.

Then we calculate the number of years by deducting 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, in this formula, we utilize 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.

With this, we now know about the Salesforce Formula Field to Calculate the Age. Next, we will move ahead and learn how to calculate the age formula field in Salesforce Lightning.

Calculate Age Formula Field in Salesforce Lightning

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

Step 1: Click the Setup icon in the top right corner of the page to access Salesforce setup.

Step 2: Click Object Manager from the menu’s left side.

Salesforce Formula Field to Calculate Age
Salesforce Formula Field to Calculate Age

Step 3: Look for the item to which we want to calculate the age. In this example, I choose the Contact object from the list of objects.

Salesforce Formula Field to Calculate Age
Salesforce Formula Field to Calculate Age of Contact

Step 4: Choose Field & Relationships.

Step 5: Next, click the New button located at the top right side of the page.

Salesforce Lightning Formula Field to Calculate Age
Salesforce Lightning Formula Field to Calculate Age

Step 6: Check the radio button in front of the Formula data type.

Step 7: To proceed click on the Next button.

Salesforce Lightning Formula Field to Calculate Age Example
Salesforce Lightning Formula Field to Calculate Age

Step 8: Enter the Field Label for the formula and the Field Name will be automatically populated. Here, I set the Field Label to Age.

Step 9: Now, check the radio button in front of Number to set it as the formula return type. In addition to this, set the decimal places to zero.

Step 10: Then, to proceed click on the Next button.

Calculate Age Formula Field in Salesforce Lightning
Calculate Age Formula Field in Salesforce Lightning

Step 11: In advance formula editor, now enter the formula that calculates the age of the person. 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 function 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.

Step 12: To check whether we write the correct formula or not, we need to click on the Check Syntax button.

Step 13: If you want to handle the empty field and also want to define the Description and Help Text, we can add it.

Step 14: Then, click on Next.

Calculate Age Formula Field in Salesforce Lightning Example
Calculate Age Advance Formula Field in Salesforce

Step 15: 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.

Step 16: Then, click on Next.

Calculate Age Formula Field in Salesforce
Calculate Age Formula Field Level Security in Salesforce

Step 17: After that, select the Page Layout name for which the formula field is to be added.

Step 18: Finally, click on the Save button to save the formula field.

Calculate Age Formula Field in Salesforce Example
Calculate Age Formula Field Page Layout in Salesforce

We can use the formula field once we’ve created it. 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.
Age Formula Field in Salesforce Lightning
Age Formula Field in Salesforce Lightning

As a result, we now know how to use Salesforce Lightning’s advance formula to calculate the age. Now, we’ll look at how to use the advance formula in Salesforce Classic to calculate the age.

Calculate Age Formula Field in Salesforce Classic

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

Step 1: From the Salesforce Lightning to open your Salesforce Classic edition. Click on Avtar and then click Switch to Salesforce Classic.

Salesforce Classic Formula Field to Calculate Age
Switch to Salesforce Classic

Step 2: After that, click on the Setup option.

Salesforce Classic Formula Field to Calculate Age Example
Salesforce Classic Formula Field to Calculate Age Setup

Step 3: Now, scroll down to the Build section. Then under Customize, select the object for whom we want to calculate the age. Here, I choose the Contacts from the object list.

Step 4: Click on the dropdown in front of Contacts and select the Fields.

Formula Field to Calculate Age in Salesforce Classic
Formula Field to Calculate Age of Contact in Salesforce Classic

Step 5: As we scroll down the page, under Custom Fields & Relationships, click on the New button.

Formula Field to Calculate Age in Salesforce Classic Example
Formula Field to Calculate Age in Salesforce Classic Example

Step 6: Select the Formula as the field data type.

Step 7: Then, to proceed click on the Next button.

Calculate Age Formula Field in Salesforce Classic
Calculate Age Formula Field in Salesforce Classic

Step 8: Enter a Field Label for the formula, and the Field Name will be automatically populated. Here, I enter Field Label as Age.

Step 9: Now, check the radio button in front of Number to set it as the formula return type. In addition to this, set the decimal places to 0.

Step 10: Click Next, to proceed.

Calculate Age Formula Field in Salesforce Classic Example
Age Formula Field in Salesforce Classic Example

Step 11: In the advance formula editor, enter the formula that calculates the age of the person. Here, is the formula:

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

Here, we use the YEAR, MONTH, DAY, and TODAY functions with logical AND, OR, and IF function to calculate the age of the person based on their birthdate and the current date.

Step 12: Click on the Check Syntax button, it will allow us to verify whether we have entered the correct formula or not.

Step 13: We can also handle the empty field and define the description and help text if we want.

Step 14: Then to proceed, click Next.

Salesforce Classic Calculate Age Formula Field
Salesforce Classic Calculate Age Advance Formula Field

Step 15: Set the appropriate Field Level Security, visible by selecting it. Since I want it to be visible to all the users, I have checked the Visible checkbox here.

Step 16: After that, select Next.

Salesforce Classic Calculate Age Formula Field Example
Salesforce Classic Calculate Age Formula Field Example

Step 17: Select the Page Layout name for which the formula field is to be added.

Step 18: In order to save the formula field, click on the Save button.

Salesforce Classic Age Formula Field
Salesforce Classic Age Formula Field

Once the formula field has been defined, we can use it. Let’s look at an example.

  • Create a contact by opening the Contacts tab with the Birth Date field and save it.
  • Then, in the detail section, we will get the Age field, which determines the age of the person.
Salesforce Classic Age Formula Field Example
Salesforce Classic Age Formula Field Example

With this, we have understood how to define the advance formula field that calculates the age of the person, while using the Salesforce Classic mode.

Conclusion

With this, we have learned the Salesforce Formula Field to Calculate the Age. Moreover, we have learned the procedure for determining the age of the person in both Salesforce Lightning and Salesforce Classic.

In addition to this, the following are the topics that we have discussed:

  • Salesforce Formula Field to Calculate Age
  • Calculate Age Formula Field in Salesforce Lightning
  • Calculate Age Formula Field in Salesforce Classic

You may also like: