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.

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 functionDATEDIF Excel FunctionDATEDIf 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 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:

  • Enter the following formula in cell C2. “=DATEDIF(A2,B2,“Y”)”Note: For details related to the DATEDIFDATEDIf 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.

“=DATEDIF(A2,B2,“Y”)”Note: For details related to the DATEDIFDATEDIf 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.

  • 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.

  • 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.

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 functionYEARFRAC Excel FunctionYEARFRAC Excel is a built-in Excel function that is used to get the year difference between two date infractions. This function returns the difference between two dates infractions like 1.5 Years, 1.25 Years, 1.75 Years, etc. So using this function we can find the year difference between two dates accurately.read more.

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.

  • 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.

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:

“=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:

“=+(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.

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 CONCATENATECONCATENATEThe CONCATENATE function in Excel helps the user concatenate or join two or more cell values which may be in the form of characters, strings or numbers.read more and the DATEDIF functions of Excel.

The steps for the given task are listed as follows:

“=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 EditorThe 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

This has been a guide to calculating age in Excel. Here we discuss how to calculate age in an Excel sheet using the DATEDIF formula, CONCATENATE formula, and Excel VBA code along with practical examples and a downloadable template. You may learn more about Excel from the following articles –

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.

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.

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).

  • Concatenate in VBAConcatenate In VBAIn VBA, concatenation refers to joining two values or strings together with the “&” symbol (ampersand operator). We must include spaces when using the & operator, else VBA will interpret it as long.read moreCalculation of Excel Formula for GradeCustom Format of Numbers in ExcelCustom Format Of Numbers In ExcelExcel custom number formatting is nothing but making the data look better or visually appealing. Excel has many inbuilt number formatting. On top of this, we can customize the Excel number formatting by changing the format of the numbers.read moreInsert Row Keyboard Shortcut in ExcelInsert Row Keyboard Shortcut In ExcelThe insertion of an excel row is simply the addition of a new (blank) row to the worksheet. The insertion of a row is eased with the help of shortcuts.read moreExcel Create ListExcel Create ListA list can be created in Excel to define a list of items/values as predefined values. It may be created using the Data Validation tool so that users may select from a list rather than entering their own values.read more