Adding and subtracting dates

[Guest]

I am trying to subtract one date from another and have the answer display as
yy/mm/dd. For example: If I subtract 2/18/1985 from 8/1/2006, the answer
should be 21/5/13 (21 years, 5 months, 13 days). For some reason, when I put
in the formula, it returns the answer 21/6/12 (21 years, 6 months, 12 days).
The formula I am putting in is +A1-B1 where A1=8/1/2006 and B1=2/18/1985. I
formatted the cell with the answer to a custom format of yy/mm/dd. I would
appreciate any suggestions.

 

[Biff]

See this:

http://cpearson.com/excel/datedif.htm

Biff

 

[Guest]

Dates are stored as numbers so when those two dates are subtracted you get
7834, which is June 12, 1921, or 7,834 days from 1/0/1900 (m/d/yyyy). See
help for more info on how excel stores dates/times and the 1900 date system.

You could use Datedif to get the difference in the format you want, but when
subtracting those two dates, I get 21 years, 5 months, 14 days.
=DATEDIF(B1,A1,"Y")&"/"&DATEDIF(B1,A1,"YM")&"/"&DATEDIF(B1,A1,"MD")

If you need 13 days for your purposes
=DATEDIF(B1,A1,"Y")&"/"&DATEDIF(B1,A1,"YM")&"/"&DATEDIF(B1,A1,"MD")-1

 

[Guest]

Thank you very much .....

See this:

http://cpearson.com/excel/datedif.htm

Biff