Calculate Age in Excel

Buzz

Ngày cập nhật gần nhất: 15/3/2026

Content

Function for Calculating Age from Birth Year in Excel

How to calculate age in Excel 2003, 2013, 2010, 2007, 2016

Learn how to calculate age in Excel using the YEAR and DATEDIF functions in the article below. Refer to effectively use Excel spreadsheets, easily and accurately calculate ages.

Excel offers numerous functions for general calculations like SUM... and specific age calculations such as YEAR... However, applying these functions accurately can be challenging, especially considering non-numeric characters in Excel cells require formatting. To calculate age in Excel, follow the instructions below.

Function for Calculating Age from Birth Year in Excel

How to calculate age in Excel 2003, 2013, 2010, 2007, 2016

Specifically, dear reader encountered an issue when entering the formula: =YEAR(NOW())-YEAR(D6) to calculate age, the result shows 1/27/1900, instead of the age.

This error occurs because the result cell is in date format, we just need to change the format of this cell to Number type. The conversion process is as follows.

Step 1: Select all cells in the Age column

Step 2: Right-click on the selected cells and choose Format Cell...

Step 3: In the dialog that appears, select the Number category. Here you can see the Decimal places section where you choose the number of decimal places to display. Since we are calculating age, which is definitely an integer, you can set it to 0. Then press OK.

The displayed result will be 27, the accurate age of Pham Thanh Tu up to the current time.

Additionally, we can use the DATEDIF function to calculate age.

- Enter the formula =DATEDIF(D7,NOW(),'y'). into cell E7. Then press Enter

The result displayed will be the age of the employee.

Above is the guide on how to calculate age in Excel using the DATEDIF and YEAR functions. Hopefully, through this article, you can understand and easily perform age calculations in Excel. Excel also offers many other date functions that you can refer to in order to innovate your methods, each Excel date function has its own syntax.

If you encounter difficulties during the process, don't hesitate to comment below, the technical team at Mytour will support you.

Frequently Asked Questions

How can I calculate age in Excel using the YEAR function?

To calculate age using the YEAR function in Excel, input the formula =YEAR(NOW())-YEAR(birth_date_cell). Ensure that the result cell is formatted as a Number, not a Date, to avoid errors.

What is the DATEDIF function and how is it used to calculate age?

The DATEDIF function calculates the difference between two dates. To find age, use the formula =DATEDIF(birth_date_cell, NOW(), 'y'). This will yield the person's age in complete years.

What should I do if the age calculation displays an incorrect date format?

If the age calculation shows a date format instead of a number, right-click the result cell, select Format Cells, and change the format to Number with zero decimal places to correct it.

Can I calculate age in older versions of Excel like 2003 or 2010?

Yes, you can calculate age in older versions of Excel, including 2003 and 2010, using the same YEAR and DATEDIF functions by entering the appropriate formulas into the cells.

Mytour's content is for customer care and travel encouragement only, and we are not responsible.

For errors or inappropriate content, please contact us at: [email protected]