On Mon, 23 May 2005 12:15:21 +0530, "Mangesh"
wrote:

Hi Ron,

thanks for the bug. The following should sort it out.

=(YEAR(B1)-YEAR(A1))-IF(AND(YEAR(A1)<YEAR(B1),OR(MONTH(A1)MONTH(B1),AN D(MON
TH(A1)=MONTH(B1),DAY(A1)DAY(B1)))),1,0) & " " &
(MONTH(B1)-MONTH(A1))-IF(AND(MONTH(A1)<MONTH(B1),DAY(A1)DAY(B1)),1,0)+I F(AN
D(YEAR(A1)<YEAR(B1),MONTH(A1)MONTH(B1),DAY(A1)<= DAY(B1)),12,0)+IF(AND(YEAR(
A1)<YEAR(B1),MONTH(A1)=MONTH(B1),DAY(A1)DAY(B1) ),11,0) & " " &
IF(DAY(B1)=DAY(A1),DAY(B1)-DAY(A1),IF(DAY(DATE(YEAR(B1),MONTH(B1),1)-1)-DAY
(A1)<0,0,DAY(DATE(YEAR(B1),MONTH(B1),1)-1)-DAY(A1))+DAY(B1))

A1 is start date, and B1 is end date.

Rules:
1. Exclude the start date, and include the end date; as excel does in its
own calculations. For e.g.
23/05/2005 - 22/05/2005 = 1 day

2. As for your other 2 queries:
28 Feb 2005 to 1 Jan 2006
27 Feb 2005 to 28 Mar 2005

Here, the year and month is completed on the same 'day' (28 of start month)
of the previous month (dec 2005) of the end day (jan 2006). So the new start
date for the calculation of the number of days only is 28 dec 2005. From
here on the remaining days are calculated. If this new start date falls on
say 30 feb (error intended after the above calculation), then it is rounded
down to a valid end date like 28 feb for non-leap years and 29 feb for leap.

Comments awaited....

I think I may have come across an "illogicality" in your formula.

StartDate: 12/31/2004
End Date: 4/30/2004

(or any similar sequence where the ending month ends prior to the beginning
bonth).

To my way of thinking, this should give a result of 4 months, exactly.

Your formula gives 3 months 30 days


By the way, for interest, here is a UDF I cobbled together to do it the way *I*
would want to see it. In other words, Calendar Months (full months) plus add
on the extra days at the beginning and end. This can leave one with a result
of

1/1/2005
4/29/2005

0 yrs 2 months 59 days

Since the only full months in this sequence are February and March.

As written, the UDF also requires a reference to the ATP, but the eomonth
function could certainly be rewritten in VBA.

===================================
Function CalendarMonthsAndDays(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

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

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

yr = Int(i / 12)
mnth = i Mod 12
dy = d2 - eomonth(d1, i) + (eomonth(d1, 0) - d1)

CalendarMonthsAndDays = yr & " yrs " & mnth & " months " & dy & " days"
End Function
==================================
--ron