How to Calculate Age in Microsoft Excel

Microsoft Excel is a powerful tool that simplifies complex calculations. One common task is figuring out a person’s age based on their date of birth. In this article, we’ll provide a detailed guide on how to do this in Excel using different methods. We’ll walk you through each step with clear examples.

Microsoft Excel Age Calculation Methods

Method 1: Calculate Age Using the DATEDIF Function

The DATEDIF function in Excel helps find the difference between two dates in years, months, or days. Follow these steps:

1. Enter the Date of Birth: Put the person’s date of birth in a cell. Make sure to use the correct date format (e.g., DD/MM/YYYY or MM/DD/YYYY) for consistency.
2. Enter the Current Date: In a separate cell, input the current date. You can use the TODAY() function for this. For instance, if the current date is in cell A1, type “=TODAY()” in cell A1.
3. Calculate the Age: In a new cell, enter the formula: “=DATEDIF(DateOfBirthCell, CurrentDateCell, “Y”)”. Replace “DateOfBirthCell” with the cell containing the date of birth, and “CurrentDateCell” with the cell containing the current date. The “Y” specifies the result in years.

Example: If date of birth is in B2 and current date is in A1, the formula would be “=DATEDIF(B2, A1, “Y”)”.

Method 2: Calculate Age Using the YEAR and TODAY Functions

This method subtracts the birth year from the current year. It’s simple and doesn’t require the DATEDIF function.

1. Enter the Birth Year: Input the person’s birth year in a cell.
2. Calculate the Age: In another cell, subtract the birth year from the current year using the formula “=YEAR(TODAY()) – BirthYearCell”. Replace “BirthYearCell” with the cell containing the birth year.

Example: If date of birth is in B2, the formula would be “=YEAR(TODAY()) – B2”.

Method 3: Calculate Age Without a Cell Reference

If you don’t have the date of birth in a specific cell, you can directly input the birth year and calculate the age using the TODAY() function.

1. Enter the Birth Year: In a cell, enter the person’s birth year.
2. Calculate the Age: In another cell, subtract the birth year from the current year using the formula “=YEAR(TODAY()) – BirthYear”. Replace “BirthYear” with the actual birth year.

Example: If the birth year is 1990, the formula would be “=YEAR(TODAY()) – 1990”.

Formatting the Age Cell: To ensure the age is displayed correctly, you may need to format the age cell. Right-click on the cell, select “Format Cells,” choose the “Number” tab, and select “General” or “Number” format. Click “OK” to apply the formatting.

Conclusion:

Calculating age in Microsoft Excel is a straightforward process that can save you time and effort. Whether you choose to use the DATEDIF function or the YEAR function with TODAY, Excel provides versatile methods for this task.