How to Convert Birth Date to Age in Excel

By Danielle Cort

Trying to manually compute how old each person on a list is can be tedious and would require recalculation every time someone passed another birthday. Fortunately, Excel offers a function that calculates age from a given date. You have the option of calculating age as of today or as of a target date.

Step 1

Open Excel and bring up the document with birth dates you want to convert.

Step 2

Enter birth dates for each person in one column if there are no birth dates in your spreadsheet yet.

Step 3

Click on an empty cell where you want the age to appear. To make it easy, create a column next to the birth date column and label it "Age." For example, if birth dates are in column B, age might be in column C.

Step 4

Enter the following formula in the cell and press "Enter":=DATEDIF(C2,TODAY(),"Y")Where it says "C2," replace this with the cell number that contains the first birth date. Using "TODAY" will calculate the person's age as of today. If you want to calculate age based on a specific date, enter that date in another cell and use that cell number in the formula instead of "TODAY()." For example, if you want to know a person's age on July 1, 2010, you would enter 7/1/2010 in cell K2 for example, and enter =DATEDIF(C2,$K$2,"Y") in your age column cells. The "$" sign fixes the reference to a single cell so that when you copy the formula, the cell reference will not change.

Step 5

Copy this cell down the column to calculate age for all your cases.

Tips & Warnings

  • If you use the TODAY() function in your formula, every time you open the spreadsheet on a different day, you will get updated age results for people whose birthdays have passed.