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.

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

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.

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,).” 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, is calculated as per the US or the European conventions respectively.
  • For “basis” equal to 1, 2 or 3, , or 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:

1. Calculate the time gap (age or period) between two dates in completed months.
2. 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.

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

Step 1: Open the VBA editor 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 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.