Working with numeric data in Excel may seem complicated, but the truth is that there are many functions and shortcuts in the editor that make this task easier. One such case is the calculation of an individual’s age, which does not need to be counted separately if you have their date of birth. Here’s how to calculate the age in Excel using a simple formula and save time and convenience in creating your documents.
If one of the information in your spreadsheet is a person’s date of birth, instead of doing the math manually to find out their age, use the YEAR and NOW functions to make this calculation automatic. To do this, follow the steps:
Table of Contents
Enter date of birth:
Confirm that the individual’s date of birth is already in the spreadsheet. It can be placed anywhere in the document, as long as it’s located alone in a cell (which we’ll call a reference cell). In addition, the date must be formatted as follows: DAY/MONTH/YEAR, with the full year, no abbreviations;
Enter the formula:
Click on the cell that must be filled in with the person’s age and write the following formula “=(YEAR(NOW())-YEAR(Y2))”.
Note that the “A2” part of the formula is highlighted.
It’s this snippet that we need to replace to do the calculation;
Fill in the column and row in the formula:
Replace the “A2” section by the column and row, respectively, of your reference cell (in the example of the image, the birth date of Luana Vila). Therefore, if the date of birth is located in column C and in line 2, as shown in the image, the formula should look like “=(YEAR(NOW())-YEAR(C2))”;
Generate age:
Hit enter, causing the subject’s calculated age to appear;
In case of problem, format the cell:
If the cell does not display the age, it is likely that it is incorrectly formatted. To fix this problem, click on it with the right mouse button. Tap on “Number Format”, click on the “General” option and confirm with “Ok”. There, the person’s age will appear correctly in the cell.
How to calculate age in Excel without cell reference
It is possible to calculate a person’s age in Excel, even if their date of birth is not entered in any cell in the spreadsheet. In these cases, you just need to know your year of birth and follow the steps below:
- In the Excel spreadsheet, click on the cell that must be filled in with the person’s age and write the following formula “=YEAR(NOW())-1960”;
- Replace “1960” with the year the person was born;
- Hit enter, causing the subject’s calculated age to appear;
- If the cell does not display the age, it is likely that it is incorrectly formatted. To fix this problem, click on it with the right mouse button. Tap on “Number Format”, click on the “General” option and confirm with “Ok”. There, the person’s age will appear correctly in the cell.
If you do not know your age, we suggest you make use free date of birth calculator or check out beam calculator for this purpose. Then go for put the date of birth in excel to pursue the calculations.
