Sunday, December 20, 2009

Excel Lesson: Age Calculation

You can calculate a persons age based on their birthday and todays date.

The calculation uses the DATEDIF() function.

The DATEDIF() is not documented in Excel 5, 7 or 97, but it is in 2000.

(Makes you wonder what else Microsoft forgot to tell us!)

Birth date : 01-Jan-60

Years lived : 49 =DATEDIF(C8,TODAY(),"y")

and the months : 11 =DATEDIF(C8,TODAY(),"ym")

and the days : 19 =DATEDIF(C8,TODAY(),"md")

You can put this all together in one calculation, which creates a text version.

Age is 49 Years, 11 Months and 19 Days

="Age is "&DATEDIF(C8,TODAY(),"y")&" Years, "&DATEDIF(C8,TODAY(),"ym")&" Months and "&DATEDIF(C8,TODAY(),"md")&" Days"

Another way to calculate age

This method gives you an age which may potentially have decimal places representing the months.

If the age is 20.5, the .5 represents 6 months.

Birth date : 01-Jan-60

Age is : 49.97 =(TODAY()-C23)/365.25

No comments:

Post a Comment