On Tue, 24 May 2005 11:14:03 +0530, "Mangesh"
wrote:
Ron, my whole idea at the moment is to avoid using any VBA and the analysis
toolpak. And thats why I am trying to work my way through simple if
conditions.
Sometimes, VBA is easier.
It is certainly simpler to debug <g.
Avoiding the ATP is easy. I would just have to write an EOMONTH function in
VBA.
With your latest iteration, I think the following VBA routine mimics it, and
seems simpler:
==========================
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
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
DateIntvl = yr & " yrs " & mnth & " months " & dy & " days"
End Function
=======================
However, both it and your latest formula give the following results, which seem
less than useful:
1/28/2005 2/28/2005 0 1 0
1/29/2005 2/28/2005 0 1 0
1/30/2005 2/28/2005 0 1 0
1/31/2005 2/28/2005 0 1 0
---------------------------------
My Calendar Month routine, (rewritten below so as to avoid the ATP reference),
gives the following results for those same date intervals:
1/28/2005 2/28/2005 0 yrs 1 months 3 days
1/29/2005 2/28/2005 0 yrs 1 months 2 days
1/30/2005 2/28/2005 0 yrs 1 months 1 days
1/31/2005 2/28/2005 0 yrs 1 months 0 days
====================================
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 = EOM(d1, i)
Loop
If temp < d2 Then
i = i - 1
End If
yr = Int(i / 12)
mnth = i Mod 12
dy = d2 - EOM(d1, i) + (EOM(d1, 0) - d1)
CalendarMonthsAndDays = yr & " yrs " & mnth & " months " & dy & " days"
End Function
'---------------------
Function EOM(DT As Date, mnths As Double) As Date
Dim Day1ofDT As Date
Dim temp As Date
Day1ofDT = DT - Day(DT) + 1
'add requisite number of months
temp = DateAdd("m", mnths, Day1ofDT)
'go to end of month
EOM = temp + 32 - Day(temp + 32)
End Function
===============================
--ron
|