date formula - STUCK!

[gino59]

Hi all!

I have two date values -
A1 = TODAY()
A2 = 12/8/1926

Using the following formula in cell D1:

=YEAR(A2)-YEAR(A1)-IF(OR(MONTH(A2)<MONTH(A1),AND(MONTH(A2)=MONTH(A1), DAY(A2)<DAY(A1))),1,0)&" years, "&MONTH(A2)-MONTH(A1)+IF(AND(MONTH(A2)>=DAY(A1)),12,IF(AND(MONTH(A2)>MONTH(A1),DAY(A2)<DAY(A1)),-1))&" months,"&A2-DATE(YEAR(A2),MONTH(A2)-IF(DAY(A2)<DAY(A1),1,0),DAY(A1))&" days"

I get -85 years, 8 months, 15 days

Not sure why it comes up with a negative for the years and also, it doesn't seem to be calculating correctly as the difference should be 84 years, 3 months, 15 days.

Any ideas where I'm going wrong?

Many many thanks!!

Cheers,
Gino

 

[gino59]

Also, if I flip the values in A1 and A2 so that A1 = 12/8/1926 and A2 = TODAY(), I get 84 years, -9 months, 15 days in cell D1...

 

[T. Valko]

Hi all!

I have two date values -
A1 = TODAY()
A2 = 12/8/1926

Using the following formula in cell D1:


=YEAR(A2)-YEAR(A1)-IF(OR(MONTH(A2)<MONTH(A1),AND(MONTH(A2)=MONTH(A1), ?&MONTH(A2)-MONTH(A1)+IF(AND(MONTH(A2) years, DAY(A2)<DAY(A1))),1,0)&?>
I get -85 years, 8 months, 15 days

Not sure why it comes up with a negative for the years and also, it doesn't seem to be calculating correctly as the difference should be 84 years, 3 months, 15 days.

Any ideas where I'm going wrong?

Many many thanks!!

Cheers,
Gino

See this...

http://www.cpearson.com/Excel/datedif.aspx

 

[VoG]

Try

=DATEDIF(A2,TODAY(),"y")&" y "&DATEDIF(A2,TODAY(),"ym")&" m "&DATEDIF(A2,TODAY(),"md")&" d"

 

[gino59]

Thanks, VOG - perfect! And for T.Valko - thanks too - I had been trying the DATEDIF formula but without success. The link has some really good info!

Many thanks as always!
Cheers!
Gino

 

[javalover]

Biff...great link, thanks!