How to calculate age in excel: Formulas and Application

  • Whatsapp

In this article, we will discuss how to calculate age in Excel. The age that is meant here is not only the age of humans, but it can also be at other creatures or objects.

In Excel, we can calculate age in several ways. How to calculate age can depend on the formula you use.

The thing to keep in mind when calculating age in Excel is the units you want to use.

You can calculate age in units of years, months or days.

Calculating Age in Year(s)

A year is a unit that is often used when we want to calculate someone’s age.

To calculate age in years, you can use the following formula:

= (end_date-start_date) / 365

Where:

start_date: Birthdate

end_date: Today Date

The weakness of the formula above is that not all years have the same day, which is 365 days. Apart from 365, there are also years which have a total of 366 days. This year is called a leap year, where February consists of 29 days.

Leap years or those with 366 days are years in which divided by four; the value does not remain. For example, 2016 is divisible by 4, 2020 is divisible by 4.

As an alternative to dividing by 365, you can use division by 365.25. The basis for the addition is 0.25 (1/4) because a leap year occurs every four years. This method also has a disadvantage because it is not very accurate to use for a baby whose age is not yet four years old or who can miss several months or days in decimal form for calculations at certain times.

Calculating Age in Years Using the YEARFRAC Function

A more accurate way to calculate age in years is to use the YEARFRAC function.

The YEARFRAC function has the following formulation:

YEARFRAC (start_date, end_date, [base])

The explanation of the arguments in the above function is as follows:

start_date Is the start date or can be filled with the date of birth.

end_date Is the end date or can be filled with the date you want to calculate the difference with the date of birth (beginning).

base: It is an argument which value consists of 0 to 4, where:

Calculating Age in Years Using the YEARFRAC Function

You can visit the following page for more details on explaining the arguments above: click here.

For more accurate results, you can use argument that are valued at 1.

Examples of using the YEARFRAC formula:

Example Calculating Age in Years Using the YEARFRAC Function

In the example above TODAY () was taken on 11 Dec 2020. The decimal number in the example above shows that there is a remaining age in months and days.

If you only need the numbers in years, you can remove the decimals by using the ROUNDDOWN function. Use argument 0 to remove decimals.

The formula used to calculate age in years in Excel becomes:

Example 2 Calculating Age in Years Using the YEARFRAC Function

= ROUNDDOWN (YEARFRAC (C2, TODAY (), 1), 0)

Calculating Age in Excel with DATEDIF Function

Another way to calculate age in Excel is to use the DATEDIF function.

To use this function, use the formula:

DATEDIF (start_date, end_date, unit)

With this function, you can display data in a complete form, which can be in years, months or days according to the units you use in the formula.

To see age in years, you can use the y unit as follows:

DATEDIF (start_date, end_date, “y”)

Example:

Calculating Age in Excel Using the DATEDIF

As in the previous example, the number of years is the same.

How to Get Age in Years, Months and Days

Apart from years, you can also perform a more detailed age calculation by creating a formula to calculate data in years, months and days.

To calculate it, use the following formula:

• Calculating the number of ages in years: = DATEDIF (start_date, end_date, “Y”)

• Calculating the number of months after deducting the year: = DATEDIF (start_date, end_date, “YM”)

• Calculating the number of days after deducting the year and month: = DATEDIF (start_date, end_date, “MD”)

We can write the above formulas in 3 different columns in Excel, or we can combine by using the CONCATENATE function or the & operator.

If written in 3 different Excel columns, the results are as follows:

Calculating Age in Excel Using the DATEDIF

You can also combine the writing text of the year, month and day above as follows:

= DATEDIF (start_date, end_date, “Y”) & “Years,” & DATEDIF (start_date, end_date, “YM”) & “Month,” & DATEDIF (start_date, end_date, “MD”) & “Days.”

If the formula above is applied, the results are as follows:

Calculating Age in Excel Using the DATEDIF

You can hide Years, Month and Days if you find that their respective values ​​are zero. Use the following formula:

= IF (DATEDIF (start_date, end_date, “y”) = 0, “”, DATEDIF (start_date, end_date, “y”) & “years,”) & IF (DATEDIF (start_date, end_date, “ym”) = 0 , “”, DATEDIF (start_date, end_date, “ym”) & “months,”) & IF (DATEDIF (start_date, end_date, “md”) = 0, “”, DATEDIF (start_date, end_date, “md”) & “days”)

Examples of using the formula as below:

How to Calculate Age in Excel using DateDif

If you look at the formula above, it still has a weakness, namely when the month and day are zero, the comma does not disappear behind Years (see row seven).

I challenge you to make better formula writing by using a more detailed IF function. Please write in the comments column if you wish.

Note: You can find the age or distance between two dates by replacing the end_date argument in the example above with any other date (other than TODAY ()). To make it more flexible, you can also reference end_date to certain cells in Excel.

Determining a person’s Nth Age Date

Suppose you have information on someone’s birth date. Then you want to find out on what date the person turns 40?

You can use the following functions:

DATE (year, month, day)

The description of the above functions is as follows (taken from the Microsoft site):

How to Calculate Age in Excel using Date
Date Function in Excel

Suppose you have information on someone’s birth date, for example, July 30, 1984. When was the person 40 years old?

In the formula, you enter data to be as the following:

= DATE (YEAR (C2) + 40, MONTH (C2), DAY (C2))

Determining a person's Nth Age Date 1

Or you can also formulate the following:

Determining a person's Nth Age Date 2

In the example above, the number 40 is referenced to cell F2.

Calculating age from date of birth separated by year, month and date

What if the date data is separated into three different columns, for example, in years, months and days?

You can first combine year, month and day data with the DATE function.

AGE-CALCULATION-USING-DATE

After that, you can calculate the age as in the example below in the Age column; the results are as follows:

Calculate the age in Excel

Or you can enter the formula as follows:

= DATEDIF (DATE (C2, MONTH (DATEVALUE (D2 & “1”)), E2), TODAY (), “y”) & “Years,” & DATEDIF (DATE (C2, MONTH (DATEVALUE (D2 & “1”) ), E2), TODAY (), “ym”) & “Months,” & DATEDIF (DATE (C2, MONTH (DATEVALUE (D2 & “1”)), E2), TODAY (), “md”) & “Days “

Calculating Age in Excel Using the DATEDIF

Creating Age Calculator in Excel

To make an age calculator in Excel, you need to reuse the formulas that have been discussed above.

Before calculating age, there are some data that need to be filled in first, namely:

• Date of birth data.

• End date data (the date on which age is calculated). The end date can be selected today’s date or a specific date that you input.

The following is an example of an age calculation form.

At the age filling form above, the process that is done first is:

• Users fill in the date of birth column

• There is validation that the date of birth that is entered cannot be greater than today’s date.

• User can choose whether to calculate age from date of birth to date of today or until a certain date that is entered in Excel.

• There is a validation that a specific date must be filled in greater than the date of birth.

After we know the desired process, first provide the column as discussed, namely:

Fill Birth Date Column: Create a validation in this column so that the birth date does not exceed today’s date.

• Column Option Button: Age as of Today and Specific Date

Enter the Option button by accessing the Developer tab >> Insert >> selecting Option Button.

Creating Age Calculator in Excel - Add Option Button

Hover your mouse over cells B11 and B12, then left-click to create an option button.

With the Option Button still selected, access the Developer menu >> Properties >> Go to the Control tab.

Creating Age Calculator in Excel - Format Option Button

In the Cell link column, click the button and point your link to a cell in Excel. In my example point to cell F2.

Link to F2 to assign the value of the Option button that was clicked. For example, if the user clicks on the Age as of Today option, the value in F2 becomes 1 or clicks on the Specific Date, then the value in F2 becomes 2.

Specific Date option allows the user to enter the date in cell C12.

Creating Age Calculator in Excel

In cell C12, there is also data validation so that the user does not enter a date that is smaller than the date of birth.

In the age calculation section, there are four columns to display age data in different formats, namely:

• Column Age (y, m, d) to display the age in years, months and days. The formula for this column (C14):

= IF ($ F $ 2 = 1, IF (DATEDIF (C9, C11, “y”) = 0, “”, DATEDIF (C9, C11, “y”) & “year (s)”) & IF (DATEDIF ( C9, C11, “ym”) = 0, “”, DATEDIF (C9, C11, “ym”) & “month (s)”) & IF (DATEDIF (C9, C11, “md”) = 0, “” , DATEDIF (C9, C11, “md”) & “day (s)”), IF (ISNUMBER (C12), IF (DATEDIF (C9, C12, “y”) = 0, “”, DATEDIF (C9, C12 , “y”) & “year (s)”) & IF (DATEDIF (C9, C12, “ym”) = 0, “”, DATEDIF (C9, C12, “ym”) & “month (s)”) & IF (DATEDIF (C9, C12, “md”) = 0, “”, DATEDIF (C9, C12, “md”) & “day (s)”), “”))

Age in Year column, to display total age in years. The formula for this column (C15):

= IF ($ F $ 2 = 1, IF (DATEDIF (C9, C11, “y”) = 0, “”, ROUND (YEARFRAC (C2, TODAY (), 1), 2) & “year (s)”) , IF (ISNUMBER (C12), IF (DATEDIF (C9, C12, “y”) = 0, “”, ROUND (YEARFRAC (C7, C12,1), 2) & “year (s)”), “” ))

Age in Month column, to display the total age in months. The formula for this column:

= IF ($ F $ 2 = 1, DATEDIF ($ C $ 9, TODAY (), “m”) & “month (s)”, IF (ISNUMBER ($ C $ 12), DATEDIF ($ C $ 9, $ C $ 12, “m”) & “month (s)”, “”))

Age in Day column, to display total age in days. The formula for this column:

= IF ($ F $ 2 = 1, DATEDIF ($ C $ 9, TODAY (), “d”) & “day (s)”, IF (ISNUMBER ($ C $ 12), DATEDIF ($ C $ 9, $ C $ 12, “d”) & “day (s)”, “”))

You can directly implement the above formula to calculate age in Excel, as shown in the example.

You can download an example here: Age Calculator in Excel.

How to Highlight Age Using Certain Upper and Lower Age limits

Usually, when we process data related to age, there are times when we need to highlight a certain age, whether it is past or less than the set limit.

You can use Conditional Formatting to highlight certain age limits in Excel, as follows:

• Go to Home >> Conditional Formatting >> Manage Rules…

• At the Conditional Formatting Rules Manager window, click New Rule…

• At the New Formatting Rule window enter the formula = YEARFRAC ($ J6, TODAY (), 1) <$ N $ 5 (as shown).

• After that, click the Format button, to select the desired format.

• At the Format Cells window, select an orange color, then click OK. Click OK again.

• Create another Rule by clicking New Rules… once again.

• In the same way as before enter the formula = YEARFRAC ($ J6, TODAY (), 1)> = 18. Choose a green color format for the highlights. Make sure your format is correct, as shown.

• Fill in the Applies to column according to the table range that you created in your Excel. In the example, the range table is in the area = $ I $ 6: $ K $ 13.

How to calculate Age in Excel

• Continue by clicking OK and clicking the Apply button.

• An example of the results is as shown in the table below:

Excel Age Calculation Form

It can be seen from the example above that for those aged under 18; the highlight will be orange. Meanwhile, for those who are more or equal to 18 years, the highlights will be green.

For a complete example of calculating age in Excel like the example above, you can download it here.

Gravatar Image

Related posts

Leave a Reply

Your email address will not be published. Required fields are marked *