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

Samsung Galaxy S9 review: Hands-on

The art of 'British' pulp fiction

Best password managers for Mac

TV & streaming : comment regarder le Tournoi des Six Nations 2018 ?