I am studying my Family History. When I want to calculate the age at death, I use the formula =(B1-A1)/365.25 with cells A&B formatted to date DD/MM/YYYY. This works fine giving me years & .1 of a year e.g. 10/11/1946 (Birth in A1) and 10/02/2006 (Death in B1) giving age as 59.3 years in C1 but if I use a DOB as 10/11/1885 I get the following in C1; #value! Has anyone any idea why or even a better formula to give me years & Months (1 to 12) which will cover the 19th, 20th & 21st centuries.
Try this formula ( where the DOB is in A1 and DOD in B1) put it in C1. It should work for all dates from 1 January 1900 =DATEDIF(A1,B1,"y") & " years, " & DATEDIF(A1,B1,"ym") & " months, " & DATEDIF(A1,B1,"md") & " days"