Calculating Age

Today’s Excel tip is one of my favorites, primarily because it’s an undocumented function. Meaning you will not find it in the Help. This week’s function is “DATEDIF”. DATEDIF returns the number of years, months or days between a start date and an end date. Let’s say you have a list of names with birthdays and you would like to know the age of each individual. You can create a DATEDIF function next to each birthday and easily find out the age in years. You would accomplish this with the following:

=DATEDIF(birthday,TODAY(),”Y”)

This function uses the birthday as the start date. Typically, you will use a cell reference like “C6”.  Then the end date, I use the function “TODAY()”. This function returns today’s date. The final parameter is the unit to return, in this case using the “Y” for the number of years.

The function’s parameters are:

DATEDIF(start_date,end_date,unit)

Start_date          A date that represents the first, or starting, date of the period.

End_date            A date that represents the last, or ending, date of the period.

Unit                       The type of information you want returned.

Unit Types

Y             returns the number of years between start_date and end_date
M            returns the number of months
D             returns the number of days
YM         returns the number of months since the current year
YD          returns the number of days since the first of the current year
MD         returns the number of days since the first of the current month

Remember to check back regularly for Microsoft Office Tips and Tricks. 