Mystery Function Calculates Age in Excel

A handy but undocumented Excel function lets you calculate a person's age in years, months, or days.

I need an equation for calculating the number of years between today's date and a person's date of birth. What formula can I use to calculate age based on date of birth?

Joe Mahe

Supposing the person's date of birth is in cell A1, this formula will calculate the age: =DATEDIF(A1, TODAY(), "y"). Be sure to format the age cell as a number, not as a date. Surprisingly, unless you're running Excel 2000, you won't find this function in Help. Microsoft has supplied it in every version since Excel 5.0, but only documented it in Excel 2000 (an oversight?). DATEDIF is not mentioned in the Microsoft Knowledge Base, except for an article that lists it as a function not available in the Office Spreadsheet component. A search of http://msdn.microsoft.com for DATEDIF turns up exactly nothing on this mystery function.

DATEDIF's first date argument must be the earlier of the two dates. You can specify m rather than y to get the number of whole months between the dates or d to get the number of days. For a detailed (if unofficial) listing of the DATEDIF function's syntax, visit www.cpearson.com/excel/datedif.htm.

Neil Rubenking served as vice president and president of the San Francisco PC User Group for three years when the IBM PC was brand new. He was present at the formation of the Association of Shareware Professionals, and served on its board of directors. In 1986, PC Magazine brought Neil on board to handle the torrent of Turbo Pascal tips submitted by readers. By 1990, he had become PC Magazine's technical editor, and a coast-to-coast telecommuter. His "User to User" column supplied readers with tips...
More »