To calculate the age of an Outlook contact

Last reviewed on April 7, 2014   —  6 comments

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.

Please post long or more complicated questions at Outlookforums.

6 responses to “To calculate the age of an Outlook contact”

  1. Handsumguy Bo

    To calculate the age of an Outlook contact:
    Diane after reading your thoughts and instructions all these years, it's great to hear your voice. I have followed your video and the written instructions; however, after I paste in the formula, when I hit OK, it stops there with the message "unknown field" and I am unable to continue. I am using Outlook 2007 is that the problem or is it something else.

  2. Kathy

    Do you have a formula to calculate the number of years for the anniversary field please

  3. Mark Ogilvie

    Is there a way to generate an MS Outlook report showing clients who will turn 65 or older in the coming month? Goal is to contact them before their birthday. Then once contacted, we wouldn't want to contact them again the following year. Thanks!

Leave a Reply

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