Thursday, 9 May 2013

Excel Calculating Age from the Date of Birth

It's easy to calculate someone's age from their date of birth if you know about Excel's DATEDIF function, unfortunately it's easy to miss this function as it is not documented. I am baffled why it is never included in the list of Excel functions.

To have a formula that updates itself as time goes on you need to get the current date into your formula and you can do this using either the NOW or TODAY functions. There's no substantial difference between the two functions for this calculation; NOW gives you the current date and time, whereas TODAY just gives the current date. When you enter the function into your formula remember to include the brackets like this, NOW() or TODAY().

Excel DATEDIF function
So, to calculate someone's age in years make sure that you have entered their date of birth correctly as a proper Excel date (using slashes or dashes) and then enter the formula:

=DATEDIF(C3,NOW(),"y")

Where C3 is the cell containing the date of birth. The interval value "y" must be entered in double quotation marks. The age is calculated in whole calendar years and will update on the subject's birthday. If you want to calculate the exact age and include the months or even the days then you have to change the interval value.

To calculate the age in months for the current year, the formula is:

=DATEDIF(C3,NOW(),"ym")

And therefore, to state the age in this type of format: "24 years, 8 months" the formula is:

=DATEDIF(C3,NOW(),"y") &" years, "&DATEDIF(C3,NOW(),"ym")&" months."

No! Don't type it all in, copy and paste it from here. I always do, life's too short to have to reinvent formulas like this.

The DATEDIF Worksheet Function

Excel DATEDIF function
The function is not documented so I am going to have to be a bit boring and do the documentation here in case you want to Bookmark this page. I always find the interval values a bit difficult to remember; "y" for year is dead easy but I struggle to remember the others. The only time you see any indication that DATEDIF exists is when you type it into a cell and Excel pops up a ghostly apparition as you enter the first bracket.

Excel DATEDIF function
This function is available in all versions of Excel since way back:

=DATEDIF(Date1, Date2, Interval)

Date1 is the first date, Date2 is the second date. Date1 should be before Date2 in the calendar. Interval is the interval type and must be one of the values from the following table: 

Interval
Description
m
Complete calendar months
d
Number of days
y
Complete calendar years
ym
Months excluding years
yd
Days excluding years
md
Days excluding years and months

The first three interval values give the gross number of years, months or days between the two dates and the last three interval values work out the days and months as if the dates were in the same year and are very useful for calculating the fragments of months and days where the date difference needs to be calculated precisely.

You must enclose the interval in double quotation marks if you are including it directly in the formula or you can enter it into a cell without quotation marks and use the cell reference of the cell in the formula instead.

If Date1 is later than Date2, DATEDIF returns a #NUM! error. If either Date1 or Date2 is not a valid date, DATEDIF returns a #VALUE! error. If Interval is not one of the items listed in the table, DATEDIF returns a #NUM! error.

Related Posts

Excel-Calculations without formulas
Excel-Sorting by last name
Excel-Switching columns to rows
Excel-CTRL key tricks
Excel-Double click tricks
Excel-Converting USA dates to UK dates
Excel Top ten shortcut keys
Excel 2010 shortcut keys
Excel-AutoSum Revisited
Excel for Mac-Deleting a range and other shortcuts
Excel-Make Pivot Tables Refresh automatically

Training Courses

If you've still got that "I just don't know what I'm doing" feeling then you might like to arrange an Excel training course for yourself or with some of your colleagues. It's really easy to book one of our courses and they're great value for money. See our website for full details.

7 comments:

  1. Thanks Steve.

    Very useful.

    ReplyDelete
  2. Thanks a lot, that was very helpful.

    ReplyDelete
  3. That's excellent! Many thanks.
    Bruce

    ReplyDelete
  4. Thanks for the kind comments. The reason why DATEDIF is supported but not documented in Excel is that it's a bit buggy and does not give consistent 100% guaranteed results. However, I have used it for years and never had the slightest problem. So, check your results and double-check if it's for something important.

    If you have the streak of the geek about you and want to know all the ins and outs of the DATEDIF issue, here's a link to a very interesting discussion:

    http://www.mrexcel.com/forum/excel-questions/302028-bug-datedif-excel-2007-a.html

    ReplyDelete
  5. Thanks Steve!!! Great article and great support!!!

    ReplyDelete
  6. I am amazed to see this article.I am a regular user of Ms Excel and i know its importance. Do study Excel as it is very vast and interesting and if you are looking for the training insistitute call Excel Training NYC

    ReplyDelete
  7. Thank you, Steve, your tip is far more elegant than the "TODAY()" formulas I read of elsewhere. Works like a charm! Bookmarking this site.

    ReplyDelete