Last reviewed on April 7, 2014   —  8 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.


  1. Handsumguy Bo says

    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.

    • Diane Poremsky says

      I see wordpress messed up the code (again) - & quot ; should be double quotes. Ampersand and less than and greater than are also messed up. I'll fix the formulas.

    • Diane Poremsky says

      You can convert the birthday formula -just replace birthday with Anniversary in it.

  2. Mark Ogilvie says

    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!

    • Diane Poremsky says

      Assuming you have their birthyear, yes. You could either do it in Outlook or Copy/Export contacts to excel and do the calculations there.

      In Outlook, you can create a filtered view - use the Advanced tab: Birthday on or before 1/1/1949 and 12/31/1949

      Filter for age

  3. Phillip Hurst says

    Is there a way to implement this on a BCM 2013 contact form? On the form I tried Cuctomize/Form Layout > Add Fields > New, but there is no "formula" option for "Data type". Thanks!

Leave a Reply

Please post long or more complicated questions at OutlookForums by

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