How to Convert Birth Date to Age in Excel

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.

...
Convert a birth date to agein Excel by using the DATEDIF function.

Step

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

Step

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

Step

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

Enter the following formula in the cell and press “Enter”:

Step

\=DATEDIF(C2,TODAY(),”Y”)

Step

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

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