Prev: Date VBA
Next: add years to a cell
From: xp on 3 Mar 2010 12:56
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!
From: Frank K on 3 Mar 2010 17:49
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,
"&B1-DATE(YEAR(B1),MONTH(B1)-IF(DAY(B1)<DAY(A1),1,0),DAY(A1))&" days"
--
Frank K


"xp" wrote:

> 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!
 | 
Pages: 1
Prev: Date VBA
Next: add years to a cell