21/3/2006
20/2/2005

answer must be
1/1/1
one day and one month and one year

thanks

Try this:

=DATEDIF(A2,A1,"md")&"/"&DATEDIF(A2,A1,"ym")&"/"&DATEDIF(A2,A1,"y")


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=534015

On Wed, 19 Apr 2006 00:40:40 -0500, John James
wrote:


Try this:

=DATEDIF(A2,A1,"md")&"/"&DATEDIF(A2,A1,"ym")&"/"&DATEDIF(A2,A1,"y")

A1: 1 Mar 2006
A2: 31 Jan 2006

Res: -2/1/0


--ron

Irk!! Thanks, Ron. No wonder there's no Excel support that tells you
about the parameters in Datedif.

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

Ugly - but it looks pretty compared to the formula which allows for
negative date differences.


Ron Rosenfeld Wrote:

A1: 1 Mar 2006
A2: 31 Jan 2006

Res: -2/1/0



--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=534015

On Wed, 19 Apr 2006 16:38:13 -0500, John James
wrote:


Irk!! Thanks, Ron. No wonder there's no Excel support that tells you
about the parameters in Datedif.

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

Ugly - but it looks pretty compared to the formula which allows for
negative date differences.


Ron Rosenfeld Wrote:

A1: 1 Mar 2006
A2: 31 Jan 2006

Res: -2/1/0


There are all kinds of problems that can arise in the absence of precise
definitions for "month" and even "year".

Using your new formula, for example.

A1: 28-Feb-2006
A2: 27-Jan-2006

1/1/0

But add just one (1) day to the date in A1:

A1: 01-Mar-2006
A2: 27-Jan-2006

5/1/0

and we add four (4) days to the result!


It's these kinds of results that lead me to question people as to exactly what
they mean by "month".

One method which I've found useful is to count complete calendar months, and
then count days that are outside of those months.

In that case,

A1: 28-Feb-2006
A2: 27-Jan-2006

0 yrs 1 month 4 days

and

A1: 01-Mar-2006
A2: 27-Jan-2006

0 yrs 1 month 5 days


and even:

A1: 01-Mar-2006
A2: 31-Jan-2006

0 yrs 1 month 1 day


However, even using this method, one can also get results such as:

A1: 30-Mar-2006
A2: 01-Jan-2006

0 yrs 1 month 60 days

I have that algorithm implemented in a UDF which can also give results in
months and fractions of a month -- where the fraction is computed separately
for the first and last (non-full-calendar) months.

So the above would be:

A1: 28-Feb-2006
A2: 27-Jan-2006

1.13 months


A1: 01-Mar-2006
A2: 27-Jan-2006

1.16 months

A1: 30-Mar-2006
A2: 01-Jan-2006

2.94 months

(1 + 60/31)

If you think about this too much, you can really go crazy!

Best,




--ron

Ron,

Ron Rosenfeld Wrote:

If you think about this too much, you can really go crazy!

Too late!

I'm happy that for the examples you cited, my formula gave correct
results.
The apparent oddity you cite is accounted for by the different number
of days in January versus February.
My formula counts days first, then months, then years.
You apparently want a formula that does the reverse. I'd love to see
you post that formula, Ron!

However, my formula does fall over when years change.

Here's a revised, more complex formula for positive date differences:

=IF(DAY(A1)<DAY(A2),DATE(YEAR(A2),MONTH(A2)+1,0)-A2+DAY(A1),DAY(A1)-DAY(A2))&"/"&
IF(MONTH(A1)<=MONTH(A2),IF(DAY(A1)<DAY(A2),MONTH(A 1)-MONTH(A2)+11,MONTH(A1)-MONTH(A2)+12),
IF(DAY(A1)<DAY(A2),MONTH(A1)-MONTH(A2)-1,MONTH(A1)-MONTH(A2)))&"/"&
IF(OR(MONTH(A1)<MONTH(A2),AND(MONTH(A1)=MONTH(A2), DAY(A1)<DAY(A2))),
YEAR(A1)-YEAR(A2)-1,YEAR(A1)-YEAR(A2))

Individual components:
Days
=IF(DAY(A1)<DAY(A2),DATE(YEAR(A2),MONTH(A2)+1,0)-A2+DAY(A1),DAY(A1)-DAY(A2))

Months
IF(MONTH(A1)<=MONTH(A2),IF(DAY(A1)<DAY(A2),MONTH(A 1)-MONTH(A2)+11,MONTH(A1)-MONTH(A2)+12),
IF(DAY(A1)<DAY(A2),MONTH(A1)-MONTH(A2)-1,MONTH(A1)-MONTH(A2)))&"/"&

Years
IF(OR(MONTH(A1)<MONTH(A2),AND(MONTH(A1)=MONTH(A2), DAY(A1)<DAY(A2))),
YEAR(A1)-YEAR(A2)-1,YEAR(A1)-YEAR(A2))

Based on my (quite) limited testing it appears OK.

Surely there's a significantly simpler way without UDFs?? Come on you
Excel gurus.

If not, Microsoft surely should create a supported Datedif formula that
works, and deals with this issue,
AND with Ron's separate date differences calculation method. Date
differences shouldn't be this complex.


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=534015