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

On Fri, 21 Apr 2006 07:56:18 -0500, John James
wrote:


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!


Here are some examples with your new formula, my CalendarMonths function, and a
DateIntvl function I've also written:

-----------------
1-Mar-2006 1/1/0 Your Latest
31-Jan-2006 0 yrs 1 month 1 day My Calendar Months
0 yrs 1 month 1 day My DateIntvl
--------------------
28-Feb-2006 1/1/0 Your Latest
27-Jan-2006 0 yrs 1 month 4 days My Calendar Months
0 yrs 1 month 1 day My DateIntvl
-----------------
1-Mar-2006 5/1/0 Your Latest
27-Jan-2006 0 yrs 1 month 5 days My Calendar Months
0 yrs 1 month 2 days My DateIntvl
------------------
30-Mar-2006 29/2/0 Your Latest
1-Jan-2006 0 yrs 1 month 60 days My Calendar Months
0 yrs 2 months 29 days My DateIntvl
-----------------


Here are the UDF's:

======================
Function CalendarMonths(d1 As Date, d2 As Date, _
Optional FracMonth As Boolean = False)
'FracMonth -- output as Month+fraction of months based on
' days in the starting and ending month
'Without FracMonth, output is in years, full calendar months, and days

Dim temp As Date
Dim i As Double
Dim yr As Long, mnth As Long, dy As Long
Dim FirstFrac As Double, LastFrac As Double
Dim Yrstr As String, Mnstr As String, Dystr As String
Dim NegFlag As Boolean

NegFlag = False
If d1 d2 Then
NegFlag = True
temp = d1
d1 = d2
d2 = temp
End If

temp = 0
Do Until temp = d2
i = i + 1
temp = EOM(d1, i)
Loop

If temp < d2 Then
i = i - 1
End If

If FracMonth = True Then
FirstFrac = (EOM(d1, 0) - d1) / Day(EOM(d1, 0))
LastFrac = (d2 - EOM(d2, -1)) / Day(EOM(d2, 0))
LastFrac = LastFrac - Int(LastFrac)
CalendarMonths = i + FirstFrac + LastFrac
If NegFlag = True Then CalendarMonths = -CalendarMonths
Else
yr = Int(i / 12)
mnth = i Mod 12
dy = d2 - EOM(d1, i) + (EOM(d1, 0) - d1)
Yrstr = IIf(yr = 1, " yr ", " yrs ")
Mnstr = IIf(mnth = 1, " month ", " months ")
Dystr = IIf(dy = 1, " day", " days")
CalendarMonths = yr & Yrstr & mnth & Mnstr & dy & Dystr
If NegFlag Then CalendarMonths = "(Neg) " & CalendarMonths
End If
End Function
===========================

Function DateIntvl(d1 As Date, d2 As Date) As String
Dim temp As Date
Dim i As Double
Dim yr As Long, mnth As Long, dy As Long
Dim Yrstr As String, Mnstr As String, Dystr As String

Do Until temp d2
i = i + 1
temp = DateAdd("m", i, d1)
Loop

i = i - 1
temp = DateAdd("m", i, d1)

yr = Int(i / 12)
mnth = i Mod 12
dy = d2 - temp
Yrstr = IIf(yr = 1, " yr ", " yrs ")
Mnstr = IIf(mnth = 1, " month ", " months ")
Dystr = IIf(dy = 1, " day", " days")

DateIntvl = yr & Yrstr & mnth & Mnstr & dy & Dystr

End Function

==============================
--ron

Ron,

And all three methods apparently correctly measure what they are
attempting to measure, based on the examples you cited.

The CalendarMonths UDF is a very specific calculation for an unusual
need - it apparently calculates the number of full calendar months
between two dates and adds the left-over days on both sides. Hence the
unusual 60 days difference on your last example. There could be
variations on this formula depending upon how you wanted to treat year
differences.

The other two methods are what I would expect most people would
consider core needs, and be looking for in calculating date
differences.

My latest formula takes the higher date, then deducts years, then
months, then days.

Your DateInv UDF takes the lower date, then adds years, then months,
then days.

Both are valid, are a common need, and should in my view be catered for
with in-built, supported date difference formula parameters. The
in-built formula should additionally allow for negative date
differences, rather than falling over.

Ron, maybe if you're so motivated, you could adjust your DateInv
formula to allow a parameter for this different direction of
calculation, and even to allow for negative date differences. That
could be a relatively popular addin (or a part of a more wide-ranging
date difference addin), assuming it's not reinventing the wheel. (My
VBA skills aren't yet up to the task) Even better if Microsoft acted.
If you're not interested or don't have time, maybe it could be posted as
a challenge on the Excel programming group, and hopefully one of the MVP
sites would pick it up.


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