Calculating age at different points in time

  • Thread starter Thread starter xp
  • Start date Start date
X

xp

Using 2007, I need to calculate exact ages based on both full and partial
dates that could range from the 1400's to current day. This is for a
genealogy project.

I would like to be able to enter a birth date into a cell which could be
month-day-year if known or month-year if known or just year.

In an adjacent column, say column 2, there would be a list of dates which
would mark various points in time, like census dates, marriage, etc. These
dates could also be month-day-year if known or month-year if known or just
year.

In column 3, 4, and 5, adjacent to column 2, I need formulas that will
calculate the difference in time from birth date to the date in column 2
showing age in years, months, days (example: 35 years, 6 months, 24 days).

I know this is rather complicated to explain, if needed I will gladly try to
explain further. Thanks much in advance for your help!
 
I used this formula to calculate years, month,days between two dates. Give
it a try

=+YEAR(B1)-YEAR(A1)-IF(OR(MONTH(B1)<MONTH(A1),AND(MONTH(B1)=MONTH(A1),
DAY(B1)<DAY(A1))),1,0)&" years, "&MONTH(B1)-MONTH(A1)+IF(AND(MONTH(B1)
<=MONTH(A1),DAY(B1)<DAY(A1)),11,IF(AND(MONTH(B1)<MONTH(A1),DAY(B1)
=DAY(A1)),12,IF(AND(MONTH(B1)>MONTH(A1),DAY(B1)<DAY(A1)),-1)))&" months,
Click to expand...
"&B1-DATE(YEAR(B1),MONTH(B1)-IF(DAY(B1)<DAY(A1),1,0),DAY(A1))&" days"
 
Back
Top