How to Calculate Age in Excel Using a Date of Birth

By Steve McDonnell

Two of the most common ways to calculate age from a birth date in Excel are to use the DATEDIF function and to use standard arithmetic operators on date fields. When you use date values in an Excel spreadsheet, it's helpful to understand how date values are stored in Excel and the difference between character strings and date fields. Date fields are not compatible between Windows and Mac versions of Excel; if you create a spreadsheet with date fields on one platform and open it with the other, the date values will be incorrect.

Date Values in Excel

Excel has a special data type called Date that correctly interprets a value as a date rather than a character string. You can convert character strings to date fields by formatting the cells with the Date format or by calculating the value of a date from its text string representation using the DATEVALUE function. For example, DATEVALUE("08/05/2008") converts the character string "08/06/2008" to the date August 6, 2008 or the date June 8, 2008, depending on whether your operating system setting is Month-Day-Year or Day-Month-Year.

How Excel Stores Dates

Excel stores dates internally as serial numbers, which are floating-point numbers. The integer portion represents the number of days since January 1, 1900 in Windows, and the number of days since January 1, 1904 on a Mac, where January 1 is equal to one. The decimal portion represents the number of seconds since midnight, or zero if no time is associated with the date. For this reason, the date field on a spreadsheeet created in Windows will be four years different from the the same date field when you open the spreadsheet on a Mac.

Calculating Age With DATEDIF

The DATEDIF function in Excel is ideal for calculating age in a spreadsheet. You can instruct the function to return the number of complete years, months and days between two dates, or any combination of the three. Pass the starting date and ending date as character strings, serial numbers or date values and a combination of "Y," "M" and "D" according to how you want the results. For example, DATEDIF("10/14/2014", "05/01/1970", "YMD") returns the number of complete years, months and days between the two dates. Passing "Y" by itself returns only the number of years. Passing "YM" returns the number of complete years and months. The TODAY function returns today's date as a date value.

Calculating Age Using Arithmetic

Since dates are stored as numbers, you can use standard arithmetic operators on date values to perform your own age calculations. When you subtract two date values, the result is the number of days between the two dates. Divide the number of days by 365.25 and take the integer value of the result to get the number of whole years between the two dates. For example, if cell A1 contains a birth date, you can calculate age by entering "=INT((TODAY()-A1)/365.25)" in a spreadsheet cell.