On Tue, 6 Dec 2005 09:31:41 -0600, JMKCT
wrote:


Thought I was losing my mind when I set up some conditional formatting
based on datedif # of months. The number of months between the start
date and the end of January and the end of February (using eomonth
function) were the same. To double check - I input the following
functions manually inserting the date.

=DATEDIF("11/30/2005","02/28/2006","M")
Answer = 2

=DATEDIF("11/30/2005","01/31/2006","M")
Answer = 2

I've used upper and lower case M with no difference. Any thoughts?
I've been using the datedif function for some forecasting and I'm a bit
concerned with this problem.

Thanks, JMK

Your observations are correct and congruent with my own about the limitations
of DATEDIF.

Here is a UDF that I've devised to try to work around this problem. It gives a
choice of two possible outputs, depending on the setting of the optional
FracMonth argument.

FracMonth = FALSE then output full calendar months + the extra days in the
beginning and ending month. This can result in outputs such as 2 months 45
days.

FracMonth = TRUE then output full calendar months + a fraction computed based
on the excess days in the first and last months.

A Calendar month is a month that includes both the first and last days of the
month.

The earliest date is not counted.

Given your data, the results would be:

3 months for the first and 2 months for the second set of dates you show.

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


--ron