=YEAR(A2)-YEAR(A1)-IF(MONTH(A2)=MONTH(A1),0,1) & " " &
(MONTH(A2)-MONTH(A1))-IF(DAY(A2)=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),12 ,0)
& " " &
IF(DAY(A2)-DAY(A1)<0,DAY(DATE(YEAR(A2),MONTH(A2),1)-1)+(DAY(A2)-DAY(A1)),DAY
(A2)-DAY(A1))

A1 = start date
A2 = End date

This should work fine enough. Tested 4 cases with it:
http://excelforum.com/showthread.php?t=371874


- Mangesh




"Ron Rosenfeld" wrote in message
...
On Thu, 19 May 2005 15:57:03 -0700, "Bluenose"
wrote:

Hello.
I have a function that allows me to calculate the length of time between
two
dates.

=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"

I have a list of dates in adjacent columns. I want to be able to display
the
result of this function in a third column, for the two dates in each row.
Can anyone tell me whether or not it is possible to somehow do this more
easily than pasting the function into each cell of the 3rd column and
changing the cell references in the funtion manually?
This is not practical as the columns are too large!
I only have a limited knowledge of Excel so I apologise if this is a
simple
query.
Hope you clever people can help!
Many thanks

Any result expressed in years, months and days will be inexact since a
"month"
can be anywhere from 28-31 days.

So is some rough approximation OK, or do you want to set up rules for what
to
do if, for example, your dates are 31 Jan 2005; 1 Mar 2005.

Both your formula and the DATEDIF() formula posted by PC give a result of

0 years, 1 months, -2 days



--ron