On UrbanPro

View My Profile       Proud to be a Teacher       Write a Review  

Tuesday, May 13, 2014

Age Calculation in MS-Excel By Using DATEDIF() Formula

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!)




B
C


8
Birth date :
1-Jan-60







10
Years lived :
54
 =DATEDIF(C8,TODAY(),"y")
11
and the months :
6
 =DATEDIF(C8,TODAY(),"ym")
12
and the days :
9
 =DATEDIF(C8,TODAY(),"md")



You can put this all together in one calculation, which creates a text version.
Age is 54 Years, 4 Months and 11 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.




B
C


23
Birth date :
1-Jan-60







25
Age is :
54.52
 =(TODAY()-C23)/365.25

No comments:

Post a Comment

Popular Posts