Formula in excel (Office XP)

  eddiemoose 14:46 10 Feb 2006

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.

  Monoux 15:11 10 Feb 2006

I believe Excel only goes back to 1 January 1900 or 1901 for dates. Not sure how to do the second bit i.e. convert decimal part of year t90o number of months

  GroupFC 15:14 10 Feb 2006

I'm nor sure, but if my memory serves me correctly, Excel gives each date a numeric value, which I think starts at 1900, and which is why you get that result for DOBs before that year.

As for a better formula, I don't have one, but there a few excel experts around so hopefully they'll be able to come up with something!

  Monoux 15:51 10 Feb 2006

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"

  Eric10 17:08 10 Feb 2006

This may be worth looking at click here but it will only work on PCs with the add-on installed so your spreadsheet won't be portable.

  VoG II 17:09 10 Feb 2006
  eddiemoose 20:27 10 Feb 2006

Many Thanks for all your replies. 1900 is the problem. Monoux, your formula works giving a very precise yy/mm/dd answer but not before 01 Jan 1900.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

iMac Pro review

25 book design and illustration tips

iMac Pro review

Idées cadeaux pour geeks et tech addicts