Formula for Date Calculation

[Bill Helbron]

I have a spreadsheet that includes a "Birth" and "Death" column and an "Age"
column. Calculating the age is simple but what I want to do is, if the person is
still living, would like to calculate their age as of the current date and
perhaps using a different color. Any suggestions for a formula?

Bill

 

[daddylonglegs]

If you just want the age in years

=DATEDIF(A1,TODAY(),"Y")

where A1 contains the date of birt

 

[saziz]

Try this:

a1 DOB
b1 =now()
c1 =DATE(YEAR(TODAY())+(DATE(YEAR(TODAY()),MONTH(E1)
DAY(A1))<TODAY()),MONTH(A1),DAY(A1))
d1 =TEXT(G1,"dddd")

b5 = DATEDIF(A1,NOW(),"ym") & " Months"
c5 =DATEDIF(A1,NOW(),"md") & " Days"
a5 =DATEDIF(A1,NOW(),"y") & " Years


You will get age calculated in row 5 ABC
Sye

 

[Bill Helbron]

Thanks daddylonglegs & Syed,

I tried both of your solutions and ended up with the same result - both
incorrect! As an example, I have "1935" in cell C3. When I type each of your
formulas into E3, I get "101". It should be "71"! What am I doing wrong?

Bill

 

[Pete_UK]

If C3 is meant to be date of birth, then you should enter it as
6/6/1935 (or whatever), not just the year.

Hope this helps.

Pete

 

[Bill Helbron]

Hi Pete,

Problem is I don't have the complete birth date, just the year!

Bill

 

[Dave Peterson]

You can subtract just the year:

=year(today())-a1
(format the cell as General)

But depending on today's date and the actual birthdate, you could be off by one.

If someone were born in 2005, are they 0 or 1 year old?

Depends on if their birthday has passed.

 

[Bill Helbron]

Hi Dave,

Thanks! That is exactly what I wanted.

Bill