Given the date of birth, how to compute the age 'as on date' (in years and months) using MS-Excel? I worked on this problem yesterday... and as this may be useful for others I thought of posting it here.
Set up an MS-Excel sheet in the following manner (depending on your comfort with Excel, you can set this up differently also - the key thing is that the linkages between the cells must be kept intact.)
Cell B2 = Date of birth -> to be inputted
Cell D2 = Now() -> this in-built Excel function gives the date and time 'as of now'. This makes the computation of age dynamic.
Cell F2 = Copy the formula below:
=IF(B5="","?",IF(YEAR(D5)-YEAR(B5)<=0,0,IF(MONTH(D5)-MONTH(B5)<0,YEAR(D5)-YEAR(B5)-1,YEAR(D5)-YEAR(B5)))&" Yrs "&IF(YEAR(D5)-YEAR(B5)<0,0,IF((YEAR(D5)-YEAR(B5))=0,IF(MONTH(D5)-MONTH(B5)<0,0,MONTH(D5)-MONTH(B5)),IF(MONTH(D5)-MONTH(B5)<0,12+(MONTH(D5)-MONTH(B5)),MONTH(D5)-MONTH(B5))))&" Mths")
The above formula has been set-up using in-built Excel functions year(date) and month(date) and works in the following manner:
1. If date of birth is missing, "?" will be shown
2. If year/month in the date of birth is either same as or after the year/month 'as on date', "0 Yrs 0 Mths" will be shown
3. In all other cases the age 'as on date' (in years and months) will be shown
Set up an MS-Excel sheet in the following manner (depending on your comfort with Excel, you can set this up differently also - the key thing is that the linkages between the cells must be kept intact.)
Cell B2 = Date of birth -> to be inputted
Cell D2 = Now() -> this in-built Excel function gives the date and time 'as of now'. This makes the computation of age dynamic.
Cell F2 = Copy the formula below:
=IF(B5="","?",IF(YEAR(D5)-YEAR(B5)<=0,0,IF(MONTH(D5)-MONTH(B5)<0,YEAR(D5)-YEAR(B5)-1,YEAR(D5)-YEAR(B5)))&" Yrs "&IF(YEAR(D5)-YEAR(B5)<0,0,IF((YEAR(D5)-YEAR(B5))=0,IF(MONTH(D5)-MONTH(B5)<0,0,MONTH(D5)-MONTH(B5)),IF(MONTH(D5)-MONTH(B5)<0,12+(MONTH(D5)-MONTH(B5)),MONTH(D5)-MONTH(B5))))&" Mths")
The above formula has been set-up using in-built Excel functions year(date) and month(date) and works in the following manner:
1. If date of birth is missing, "?" will be shown
2. If year/month in the date of birth is either same as or after the year/month 'as on date', "0 Yrs 0 Mths" will be shown
3. In all other cases the age 'as on date' (in years and months) will be shown
No comments:
Post a Comment