How to Convert DOB to Age in MS Excel

Updated July 20, 2017

Sometimes you create an Microsoft Excel calculation that you are certain will work. Yet it does not. Attempting to convert date of birth, or DOB, to age in Excel is one of those instances. Intuition might tell you that it's as simple as subtracting today's date from the DOB. In fact, that's just the start. Getting the DOB to age conversion right requires the assistance of a few more functions. In addition, you need to pay attention to the format of your data.

Create two columns in your spreadsheet. Name the first column "DOB." Name the second column "Age."

Enter an actual DOB in the cell under the "DOB" column.

In the cell next to this actual DOB, type in the following:

\=QUOTIENT(NOW()-A2, 365.2422)

This assumes that the actual DOB is in cell A2. If this is not the case, replace A2 with the correct cell. Press the "Enter" key on your keyboard.

There are two functions used in the above equation. The NOW() function calculates the current date and time. When the DOB is subtracted from NOW(), you get the age of a person in days. The QUOTIENT() function divides the age in days by the number of days in a tropical year (365.2422). Using the tropical year eliminates errors that would arise from having to choose between the Gregorian calendar's 365-day year and 366-day leap year. Using QUOTIENT() was chosen over simple division to eliminate rounding errors. These errors might occur when trying to shorten the data to only include the whole number years of age.

Click on the cell you just exited. This is the cell with the formula in it. You will notice that the cell is in date format, which is not helpful.

Click on the "Home" tab at the top of the screen to open up the "Home" ribbon. Click on the "Number Format" (unlabeled) drop-down list in the "Number" section of the ribbon. Select "Number." You will now see the age of a person born on the DOB entered in the adjacent cell.

bibliography-icon icon for annotation tool Cite this Article

About the Author

Since 2008, Antoinette Smith has covered sports and local events for "TechNews," the student newspaper for the Illinois Institute of Technology. She holds a Bachelor of Science in professional and technical communication from the Illinois Institute of Technology.