Working out an individuals age in Years, Months, Days if they were born before 1 Jan 1900

[Donald Wickham]

Hi all,

Within my Family Tree I work out the exact age of an individual using the following formula, E19 is date of birth and E20 is date of death.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=DATEDIF(E19,E20,"y") & " years, " & DATEDIF(E19,E20,"ym") & " months, " & DATEDIF(E19,E20,"md") & " days"

I have just come across a problem with this formula for someone born before 1 Jan 1900 who dies post 1900. I would be grateful for any assistance on how to overcome this issue.
[/FONT]

 

[Taul]

Hi,
I would suggest looking here, lots of interesting stuff on pre-1900 dates and a workaround in a downloadable workbook as an example
http://www.exceluser.com/formulas/earlydates.htm

 

[Donald Wickham]

Thank you, I now have a reference point, now down to the homework to work it out.

 

[Taul]

I haven't looked at the downloadable file for a few years but I managed to find an older workbook where I used the formulas, extract blow.

A2 = Name
B2 = DoB
C2 = Date died

formula in col D2

=IF(ISBLANK(B2),"-",IF(ISBLANK(C2),"-",DATEDIF(IF(ISTEXT(B2),DATEVALUE(LEFT(B2,LEN(B2)-4)&RIGHT(B2,4)+1000),EDATE(B2,12000)),IF(ISTEXT(C2),DATEVALUE(LEFT(C2,LEN(C2)-4)&RIGHT(C2,4)+1000),EDATE(C2,12000)),"y")&" y "&DATEDIF(IF(ISTEXT(B2),DATEVALUE(LEFT(B2,LEN(B2)-4)&RIGHT(B2,4)+1000),EDATE(B2,12000)),IF(ISTEXT(C2),DATEVALUE(LEFT(C2,LEN(C2)-4)&RIGHT(C2,4)+1000),EDATE(C2,12000)),"ym") &" m " &DATEDIF(IF(ISTEXT(B2),DATEVALUE(LEFT(B2,LEN(B2)-4)&RIGHT(B2,4)+1000),EDATE(B2,12000)),IF(ISTEXT(C2),DATEVALUE(LEFT(C2,LEN(C2)-4)&RIGHT(C2,4)+1000),EDATE(C2,12000)),"md")&" d "))

 

[Donald Wickham]

Thank you that works a treat and is a great help for the Family Tree, that is definately one I will save to the Excel list, again thank you.