Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Calculations | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
PivotTable - Count by Month | Excel Worksheet Functions | |||
Count and Sum with Conditional Formatting Problem | Excel Worksheet Functions | |||
Count data entries and date problem | Excel Worksheet Functions |