Calculate Age in Excel

Top Methods to Calculate Age in Excel

In Excel, one can calculate the exact age of a person using different techniques. Age can be calculated in years, months, days, hours, and so on. For calculating the age, the beginning and ending dates need to be specified. Besides age, one can calculate the duration of a project, the time gap between two specific dates, the years of existence of an organization, etc.

For example, one can calculate the number of days between 01/16/2018 (in cell A1) and 01/30/2018 (in cell B1). The formula “=DATEDIF(A1,B1,“d”)” returns 14 days. Type the formula without the starting and ending double quotation marks.

Calculating the time period is easy if the start and end dates represent the beginning and the ending of a month respectively. However, in cases where this is not true, the complexities associated with the calculations increase. In such cases, Excel can prove to be of help.

The purpose of calculating the age is to study the period of survival of a person, event, project, corporate, and so on. It must be noted that Excel does not have a specific function for calculating age. But, different formulas of Excel can be used for the same.

How to Calculate Age in Excel?

Let us consider some examples to understand the calculation of age in Excel.

You can download this Calculate Age Excel Template here – Calculate Age Excel Template

Example #1–DATEDIF to Calculate Age in Completed Years

The following image shows two dates in each row. We want to calculate the time gap between the past date (column A) and the current date (column B).

Use the DATEDIF excel function to calculate the time gap (age) in completed years.

The steps to calculate the age in years by using the DATEDIF excel function are listed as follows:

Enter the following formula in cell C2.
“=DATEDIF(A2,B2,“Y”)”

Note: For details related to the DATEDIF DATEDIf is a date function that finds the difference between two dates, which can now be expressed in years, months, or days. This function's syntax is =DATEDIF (Start Date, End Date, Unit).read more function, refer to the “description of the DATEDIF function” given at the end of this example.
Press the “Enter” key. The output appears in cell C2, as shown in the following image. Hence, for the dates specified in row 2, the completed years of age are 4.
Drag the DATEDIF formula of cell C2 till cell C16. The outputs appear for the entire dataset. This is shown in the following image.

Description of the DATEDIF function: The syntax of the DATEDIF function is, “DATEDIF(start_date,end_date,unit).” The function accepts the following mandatory arguments:

Start date: This is the beginning date of the given period. In this example, it is the past date.
End date: This is the last date of the given period. In this example, it is the current date.
Unit: This returns the time gap between the two dates in different units. These units are years, months, and days. It can take any of the following six values:
- For “unit” equal to “y,” “m,” or “d,” the completed number of years, months or days is calculated respectively.
- For “unit” equal to “md,” “yd,” or “ym,” the days (ignoring years and months), days (ignoring years) or months (ignoring days and years) are calculated respectively.

Explanation: The DATEDIF function (entered in step 1) returns the completed years of age between any two specified dates. This is because we have set the “unit” argument at “y,” which represents years. For row 2, the completed years between 01/01/2015 and 12/30/2019 are 4.

Note: In the DATEDIF formula, the end date should always be greater than the start date. If not, Excel returns the “#NUM!” error. The DATEDIF function works in almost all versions of Excel.

However, Excel does not help while entering the arguments of the DATEDIF function, unlike in the case of the other functions. This is because the DATEDIF is a hidden function, which is not available in the Formulas tab of Excel.

Example #2–YEARFRAC to Calculate Age in Fractional Years

Working on the data of example #1, let us calculate the time gap (age) in fractions of years with the help of the YEARFRAC excel function.

The steps for the same are listed as follows:

Step 1: Enter the following formula in cell C2.

“=YEARFRAC(A2,B2,1)”

Note: For the syntax of the YEARFRAC function, refer to the “description of the YEARFRAC function” given at the end of this example.

Step 2: Press the “Enter” key. Next, drag the preceding formula to the remaining cells of column C. The outputs of column C are shown in the following image.

Description of YEARFRAC function: The syntax of the YEARFRAC function is “YEARFRAC(start_date,end_date,[basis]).” The function accepts the following arguments:

Start date: This represents the starting date (past date) of the given period.
End date: This represents the ending date (current date) of the given period.
Basis: This is the basis on which the number of days is counted while calculating the fractional years. It can take any of the following five values:
- For “basis” equal to 0 or 4, [(30 days per month)/(360 days per year)] is calculated as per the US or the European conventions respectively.
- For “basis” equal to 1, 2 or 3, [(actual days)/(actual days in the year)], [(actual days)/360] or [(actual days)/365] is calculated respectively.

The “start date” and “end date” are mandatory arguments, while “basis” is an optional argument. If the “basis” argument is omitted, Excel assumes the basis as 0.

Explanation: For row 2, the gap (in years) between 01/01/2015 and 01/01/2019 is 4. The remaining gap between 01/02/2019 and 12/30/2019 is calculated in fractions. The actual days in this period are 365-2=363. The two days that have been excluded are 01/01/2019 and 12/31/2019.

Since the basis is entered as 1, the actual days within the period are divided by the actual days within the year. Hence, 363/365=0.994521. So, the YEARFRAC function returns 4+0.99=4.99 for row 2. Likewise, the fractional years for the remaining rows are calculated by Excel.

Example #3–DATEDIF and Arithmetic Operations to Calculate Age in Completed and Fractional Months

Working on the data of example #1, we want to perform the following tasks:

Calculate the time gap (age or period) between two dates in completed months.
Calculate the time gap (age or period) between two dates in fractional months.

For task “a,” use the DATEDIF function of Excel. For task “b,” use the subtraction and division operations.

a. The steps for calculating the age (time gap) in completed months by using the DATEDIF excel function are listed as follows:

Step 1: Enter the following formula in cell C2.

“=DATEDIF(A2,B2,“M”)”

Step 2: Press the “Enter” key. The output is 59, as shown in the following image.

Step 3: Drag the DATEDIF formula till cell C16, as shown in the following image. The outputs appear in column C.

Explanation: The DATEDIF formula, entered in step 1, calculates the time gap in completed months. This is because the “m” entered in this formula corresponds to months.

For row 2, the number of completed months between 01/01/2015 and 12/30/2019 is 59. This includes 48 months of the four years (2015-2018, both inclusive) plus 11 months of the final year (2019).

The month of December 2019 has been excluded from the calculation as it is not a completed month. In this way, the number of completed months for the remaining rows has been calculated by Excel.

b. The steps for calculating the age in excel (time gap) in fractional months are listed as follows:

Step 1: Enter the following formula in cell C2.

“=+(B2-A2)/30”

Note: The formula “=(B2-A2)/30” returns the same output as the formula “=+(B2-A2)/30.” Hence, one can ignore the “plus” sign of the preceding formula.

Step 2: Press the “Enter” key. Then, drag the formula of cell C2 till cell C16. The outputs are shown in the following image.

Explanation: For row 2, Excel calculates the completed months as 59. The remaining days are divided by 30 and added to this number in decimals. To make it simpler, the total days between 01/01/2015 and 12/30/2019 are divided by 30.

So, 1824/30=60.8. Hence, the total fractional months for the second row are calculated as 60.8. Likewise, the fractional months for the remaining rows are computed by Excel.

Note: For more details on how we obtained the total days as 1824 (for row 2), refer to example #4.

Example #4–DATEDIF to Calculate Age in Days

Working on the data of example #1, we want to calculate the age (time gap) in excel in number of days. Use the DATEDIF function of Excel.

The steps for the same are listed as follows:

Step 1: Enter the following DATEDIF formula in cell C2.

“=DATEDIF(A2,B2,“d”)”

Step 2: Press the “Enter” key. The output in cell C2 is 1824, as shown in the following image.

Step 3: Drag the formula of cell C2 till cell C16. The dragging process and the resulting outputs are shown in the following image.

Explanation: For each row, Excel has calculated the number of days between the starting and the ending dates. This is because we entered “d” in the DATEDIF formula, which corresponds to days.

Example #5–CONCATENATE and DATEDIF to Calculate Age in Years, Months, and Days

Working on the data of example #1, we want to calculate the age in excel (time gap) between two dates in years, months, and days. Notice that a new date has been entered in cell A2.

Use the CONCATENATE and the DATEDIF functions of Excel.

The steps for the given task are listed as follows:

Step 1: Enter the following formula in cell C2.

“=CONCATENATE(DATEDIF(A2,B2,”Y”),” years “,DATEDIF(A2,B2,”YM”),” Months and “,DATEDIF(A2,B2,”MD”),”Days”)”

Step 2: Press the “Enter” key. The output in cell C2 is “1 years 11 months and 29 days.”

Step 3: Drag the formula to the remaining cells of column C. The outputs are shown in the following image.

Explanation: In the formula entered in step 1, we have concatenated (joined) three DATEDIF formulas with the help of the CONCATENATE function. Moreover, the text strings “years,” “months,” and “days” are entered after each DATEDIF formula. This implies that in the final output, the result of each DATEDIF formula will be joined with the respective text strings.

The following notations have been used in the formula (in step 1):

“Y”: This calculates the completed years between the two specified dates.
“YM”: This calculates the number of months over and above the completed years.
“MD”: This calculates the number of days over and above the completed months.

For row 2, the number of completed years is 1, which is counted from 01/01/2018 to 01/01/2019. The number of completed months is counted from 01/01/2019 to 12/01/2019, which equals 11. Finally, the number of days is counted from 12/01/2019 to 12/30/2019, which equals 29.

Hence, the output is 1 year, 11 months, and 29 days.

Calculate Age Using VBA

Let us calculate the time gap (age) using excel VBA. The steps for the same are listed as follows:

Step 1: Open the VBA editorVBA Editor The Visual Basic for Applications Editor is a scripting interface. These scripts are primarily responsible for the creation and execution of macros in Microsoft software.read more by pressing the keys “Alt+F11” together.

Step 2: Define the code, as shown in the following image. We have defined “age” as a variant whose source has been given as cell A1 of the worksheet.

Step 3: In the age calculator, enter the date in the mm/dd/yyyy format.

Step 4: Click the button “show age as of date.”

Step 5: The output is shown in the following image.

Explanation: The system has calculated the age from 03/28/1998 to 01/16/2019. Hence, the output is 20 years, 9 months, and 19 days.

The Cautions While Calculating Age in Excel

The cautions governing the calculation of age in Excel are listed as follows:

Ensure that two dates are necessarily specified for calculating the age in excel. From these two dates, one must precede the other. While applying the DATEDIF function, remember that the end date exceeds the start date.
Confirm that the two dates specified are recognized by Excel as valid dates. If not, Excel returns the “#VALUE!” error. If dates appear as text, convert them to dates by using the “format cells” option of Excel.
Make sure that the two dates entered are in the same format. For instance, both dates can be in the format mm/dd/yyyy.

Note: Excel stores the dates as consecutive numbers having a difference of 1. The reason for storing dates as serial numbers (like 43466) is to facilitate calculations. However, to understand such serial numbers, it is essential to format them as dates.

Frequently Asked Questions

1. What does it mean to calculate age in Excel?

In Excel, one can calculate the age beginning from a certain date. Likewise, the time gap between two dates or the time taken to complete a project can be calculated.

For calculating such time periods, one must ensure that the beginning and the ending dates specified are recognized by Excel as valid dates. Thereafter, the calculations can be performed in Excel. To calculate age between two given dates, one can use the DATEDIF or YEARFRAC functions of Excel.

Note: For the syntax of the DATEDIF and YEARFRAC functions, refer to the examples of this article.

2. How to calculate the age from the date of birth in Excel?

Let us calculate the age from a random date of birth, 10/25/2002 (in cell A1), till the current date (11/25/2021). The steps are listed as follows:

a. Enter the formula “=INT((TODAY()-A1)/365)” in any cell, say B1. Exclude the beginning and ending double quotation marks while entering the formula.

b. Press the “Enter” key.

The output 19 appears in cell B1. The INT function rounds the decimal number downwards to the nearest integer. Had we entered the formula as “=(TODAY()-A1)/365,” we would have obtained 19.098.

The TODAY function returns the current date. The difference between the current date and the date in cell A1 is carried out. The resulting output is in days. This is divided by 365 to obtain the age in number of years.

Note: The output of the formula (entered in step “a”) updates automatically to reflect the change in the current date.

3. How to calculate the age in years and months in Excel?

Let us calculate the age between 06/14/1996 (in cell A1) and the current date (11/25/2021) in years and months. The steps are listed as follows:

a. Enter the formula [=DATEDIF(A1,TODAY(),”y”)&” years, “&DATEDIF(A1,TODAY(),”ym”)& ” months “] in any cell, say C1. Exclude the beginning and ending square brackets while entering the formula.

b. Press the “Enter” key.

The output in cell C1 is “25 years, 5 months.” This is obtained without the double quotation marks but with the spaces and the comma between the numbers and the text strings.

Notice that, in the formula, we entered a space preceding and succeeding the words “years” and “months.” We also inserted a comma following the word “years.” Consequently, spaces and a comma are inserted at the respective places in the output. A space after “months” may or may not be entered in the formula.

Note: The TODAY function updates on its own each time the workbook is opened. Hence, the current date updates automatically and so does the output of the formula (entered in step a).

19th/20th September 2020	Saturday - Sunday	9 am IST to 5 pm IST
26th/27th September 2020	Saturday - Sunday	9 am IST to 5 pm IST

Calculate Age in Excel