Datedif incorrect month count - February problem??
 

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


--
JMKCT
------------------------------------------------------------------------
JMKCT's Profile: http://www.excelforum.com/member.php...o&userid=29394
View this thread: http://www.excelforum.com/showthread...hreadid=491085

Datedif incorrect month count - February problem??
 

After some experimentation, I noted that the DateDif function uses the
month-end date of the start month to decide if a month has passed.
In otherwords, if the start date has 31 days, Apr/Jun/Sep/Nov all have
issues if your start date is the 31st.

Only Feb has issues if your start date is the 30th.

Feb does NOT have problems if the year is a leap year and the start date is
the 29th.

No months have issues if your start date is less than or equal to the 28th.

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"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


--
JMKCT
------------------------------------------------------------------------
JMKCT's Profile: http://www.excelforum.com/member.php...o&userid=29394
View this thread: http://www.excelforum.com/showthread...hreadid=491085

Datedif incorrect month count - February problem??
 

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

Datedif incorrect month count - February problem??
 

I've tried using the UDF, but I get a "sub ior function not defined at
"Temp = EOM(d1, i)". Is there a reference I need to load in VBA for
this?
Thanks, JMK


--
JMKCT
------------------------------------------------------------------------
JMKCT's Profile: http://www.excelforum.com/member.php...o&userid=29394
View this thread: http://www.excelforum.com/showthread...hreadid=491085

Datedif incorrect month count - February problem??
 

I believe Ron forgot to include another UDF called EOM (End Of Month) A
quick google on Ron's name and EOM gives this


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


you can put that in the same module and now it should work (haven't tested
it but I am sure it will)


--

Regards,

Peo Sjoblom



"JMKCT" wrote in
message ...

I've tried using the UDF, but I get a "sub ior function not defined at
"Temp = EOM(d1, i)". Is there a reference I need to load in VBA for
this?
Thanks, JMK


--
JMKCT
------------------------------------------------------------------------
JMKCT's Profile:
http://www.excelforum.com/member.php...o&userid=29394
View this thread: http://www.excelforum.com/showthread...hreadid=491085