To calculate the age of an Outlook contact

Last reviewed on April 7, 2014

Fields showing a persons ageYou can add a custom field to a Microsoft Outlook contact and use the following formula to calculate the age. This code for an Outlook formula field on a Contact item or in a Contacts folder gives you the “real age” for everyone except those fortunate few born on Feb. 29 in a leap year:

IIf([Birthday]<>"None",DateDiff("yyyy",[Birthday],Date())-IIf(DateDiff("d", CDate(Month([Birthday]) & "/" & Day([Birthday]) & "/" & Year(Date())),Date())<0,1,0),"")

DateDiff by itself is inadequate, because it will round up if the birthday has not yet occurred during the current year.

A simpler formula, (Now() – [Birthday] ) /365 also rounds the closest whole age: less than 6 months until the next birthday and it reports the age at the next birthday, not the current age.

Updated formula for international date format

Jezz brought it to my attention that Sue’s formula is US-centric. It works correctly with mm/dd/yy formatted dates but not when the user is using dd/mm/yy format. There are two ways to fix this: switch the order of the month and date in this section: CDate(Month([Birthday]) & “/” & Day([Birthday]) or use a different formula.

My new formula counts the number of days between the birthday date and now, divides by 365.25 (to account for leap years) and displays the resulting integer. Because it uses serial dates in the calculation, the short date format won’t affect the calculation.

IIf([Birthday]<>"None", Int(DateDiff("d", DateSerial(Year([Birthday]), Month([Birthday]), Day([Birthday])), DateSerial(Year(Now()), Month(Now()), Day(now())))/365.25),"")

This screenshot displays the results of Sue’s formula in the Age column, the results from my new formula in the Age2 column. The Date format in Region settings uses dd/mm/yy format.

Age formula samples

For the example shown in the screenshot, I created a custom formula field in the folder and added the formula to it.

How to create a custom field tutorial

Age template

Custom age formTemplate showing the Contacts age – because this custom form has a custom field, it either needs to be published or opened using the Choose Form dialog in Outlook 2010 and 2007. Also, the custom fields cannot be added to the “front” page in Outlook 2003 and up. If you edit the first page, the “pretty” contact card reverts to the old contact card look in older versions of Outlook. This sample contact and formula works in all versions of Microsoft Outlook.

See How to create a custom appointment form that displays a person’s current age in Microsoft Outlook calendar for a way to add the current age to the subject line of a birthday event.

To apply a custom form to existing contacts, you need to change the message class. You can do this with DocMessageClass, a free utility used to change forms used on Outlook items.

Written by

Diane Poremsky
A Microsoft Outlook Most Valuable Professional (MVP) since 1999, Diane is the author of several books, including Outlook 2013 Absolute Beginners Book. She also created video training CDs and online training classes for Microsoft Outlook. You can find her helping people online in Outlook Forums as well as in the Microsoft Answers and TechNet forums.

If the Post Coment button disappears, press your Tab key.