software

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,quot;mdquot;)amp;quot;/quot;amp;DATEDIF(A2,A1,quot;ymquot;)amp;quot;/quot;amp;DATEDIF(A2,A1,quot;yquot;)--
John James
------------------------------------------------------------------------
John James's Profile: www.excelforum.com/member.php...oamp;userid=32690
View this thread: www.excelforum.com/showthread...hreadid=534015On Wed, 19 Apr 2006 00:40:40 -0500, John James
gt; wrote:

gt;
gt;Try this:
gt;
gt;=DATEDIF(A2,A1,quot;mdquot;)amp;quot;/quot;amp;DATEDIF(A2,A1,quot;ymquot;)amp;quot;/quot;amp;DATEDIF(A2,A1,quot;yquot;)

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)lt;DAY(A2),DATE(YEAR(A2),MONTH(A2) 1,0)-A2 DAY(A1),DAY(A1)-DAY(A2))amp;quot;/quot;amp;MONTH(A1)-MONTH(A2) IF(DAY(A1)gt;=DAY(A2),0,-1)amp;quot;/quot;amp;YEAR(A1)-YEAR(A2) IF(MONTH(A1)gt;=MONTH(A2),0,-1)

Ugly - but it looks pretty compared to the formula which allows for
negative date differences.Ron Rosenfeld Wrote:
gt;
gt; A1: 1 Mar 2006
gt; A2:31 Jan 2006
gt;
gt; Res:-2/1/0
gt;--
John James
------------------------------------------------------------------------
John James's Profile: www.excelforum.com/member.php...oamp;userid=32690
View this thread: www.excelforum.com/showthread...hreadid=534015On Wed, 19 Apr 2006 16:38:13 -0500, John James
gt; wrote:

gt;
gt;Irk!! Thanks, Ron. No wonder there's no Excel support that tells you
gt;about the parameters in Datedif.
gt;
gt;=IF(DAY(A1)lt;DAY(A2),DATE(YEAR(A2),MONTH(A2) 1,0 )-A2 DAY(A1),DAY(A1)-DAY(A2))amp;quot;/quot;amp;MONTH(A1)-MONTH(A2) IF(DAY(A1)gt;=DAY(A2),0,-1)amp;quot;/quot;amp;YEAR(A1)-YEAR(A2) IF(MONTH(A1)gt;=MONTH(A2),0,-1)
gt;
gt;Ugly - but it looks pretty compared to the formula which allows for
gt;negative date differences.
gt;
gt;
gt;Ron Rosenfeld Wrote:
gt;gt;
gt;gt; A1: 1 Mar 2006
gt;gt; A2:31 Jan 2006
gt;gt;
gt;gt; Res:-2/1/0
gt;gt;

There are all kinds of problems that can arise in the absence of precise
definitions for quot;monthquot; and even quot;yearquot;.

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 quot;monthquot;.

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 daysand even:

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

0 yrs 1 month 1 dayHowever, 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 monthsA1: 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:
gt;
gt; If you think about this too much, you can really go crazy!
gt;
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)lt;DAY(A2),DATE(YEAR(A2),MONTH(A2) 1,0)-A2 DAY(A1),DAY(A1)-DAY(A2))amp;quot;/quot;amp;
IF(MONTH(A1)lt;=MONTH(A2),IF(DAY(A1)lt;DAY(A2),MONTH(A 1)-MONTH(A2) 11,MONTH(A1)-MONTH(A2) 12),
IF(DAY(A1)lt;DAY(A2),MONTH(A1)-MONTH(A2)-1,MONTH(A1)-MONTH(A2)))amp;quot;/quot;amp;
IF(OR(MONTH(A1)lt;MONTH(A2),AND(MONTH(A1)=MONTH(A2), DAY(A1)lt;DAY(A2))),
YEAR(A1)-YEAR(A2)-1,YEAR(A1)-YEAR(A2))

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

Months
IF(MONTH(A1)lt;=MONTH(A2),IF(DAY(A1)lt;DAY(A2),MONTH(A 1)-MONTH(A2) 11,MONTH(A1)-MONTH(A2) 12),
IF(DAY(A1)lt;DAY(A2),MONTH(A1)-MONTH(A2)-1,MONTH(A1)-MONTH(A2)))amp;quot;/quot;amp;

Years
IF(OR(MONTH(A1)lt;MONTH(A2),AND(MONTH(A1)=MONTH(A2), DAY(A1)lt;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: www.excelforum.com/member.php...oamp;userid=32690
View this thread: www.excelforum.com/showthread...hreadid=534015On Fri, 21 Apr 2006 07:56:18 -0500, John James
gt; wrote:

gt;
gt;Ron,
gt;
gt;Ron Rosenfeld Wrote:
gt;gt;
gt;gt; If you think about this too much, you can really go crazy!
gt;gt;
gt;Too late!
gt;
gt;I'm happy that for the examples you cited, my formula gave correct
gt;results.
gt;The apparent oddity you cite is accounted for by the different number
gt;of days in January versus February.
gt;My formula counts days first, then months, then years.
gt;You apparently want a formula that does the reverse. I'd love to see
gt;you post that formula, Ron!
gt;

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

-----------------
1-Mar-20061/1/0Your Latest
31-Jan-20060 yrs 1 month 1 dayMy Calendar Months
0 yrs 1 month 1 dayMy DateIntvl
--------------------
28-Feb-20061/1/0Your Latest
27-Jan-20060 yrs 1 month 4 daysMy Calendar Months
0 yrs 1 month 1 dayMy DateIntvl
-----------------
1-Mar-20065/1/0Your Latest
27-Jan-20060 yrs 1 month 5 daysMy Calendar Months
0 yrs 1 month 2 daysMy DateIntvl
------------------
30-Mar-200629/2/0Your Latest
1-Jan-20060 yrs 1 month 60 daysMy Calendar Months
0 yrs 2 months 29 daysMy DateIntvl
-----------------Here are the UDF's:

======================
Function CalendarMonths(d1 As Date, d2 As Date, _
Optional FracMonth As Boolean = False)
'FracMonth --gt; 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 gt; d2 Then
NegFlag = True
temp = d1
d1 = d2
d2 = temp
End If

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

If temp lt;gt; 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, quot; yr quot;, quot; yrs quot;)
Mnstr = IIf(mnth = 1, quot; month quot;, quot; months quot;)
Dystr = IIf(dy = 1, quot; dayquot;, quot; daysquot;)
CalendarMonths = yr amp; Yrstr amp; mnth amp; Mnstr amp; dy amp; Dystr
If NegFlag Then CalendarMonths = quot;(Neg) quot; amp; 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 gt; d2
i = i 1
temp = DateAdd(quot;mquot;, i, d1)
Loop

i = i - 1
temp = DateAdd(quot;mquot;, i, d1)

yr = Int(i / 12)
mnth = i Mod 12
dy = d2 - temp
Yrstr = IIf(yr = 1, quot; yr quot;, quot; yrs quot;)
Mnstr = IIf(mnth = 1, quot; month quot;, quot; months quot;)
Dystr = IIf(dy = 1, quot; dayquot;, quot; daysquot;)

DateIntvl = yr amp; Yrstr amp; mnth amp; Mnstr amp; dy amp; 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: www.excelforum.com/member.php...oamp;userid=32690
View this thread: www.excelforum.com/showthread...hreadid=534015