DATEDIF

[ExcelChampion]

Given that the DATEDIF worksheet function is unreliable, would it not be wise to inform the users asking for assistance that, if called for, the DATEDIF function may not calculate correctly? For instance, compare the two formulas below:-

=DATEDIF("11/30/05","01/31/06","m") 

=DATEDIF("11/30/05","02/28/06","m")

It is my understanding that the reason DATEDIF is not documented is because it has a bug. Should we not preface our solution to this fact?

 

[barry houdini]

Hello Excel Champion

Given our discussion elsewhere, you probably won't be surprised at my view

I think it's entirely appropriate to warn of potential problems with any formula or function, given particular circumstances or data.

I have no idea why DATEDIF is not well documented but I'm not sure you can describe the above as a "bug" any more than the issues with floating point arithmetic.

In my view this is just the way DATEDIF works.

In a similar vein, if A1 contains the date 31-Dec-2006 and you use the formula

=DATE(YEAR(A1),MONTH(A1)+2,DAY(A1))

you get a result of 3-Mar-2007

Does DATE have a bug?

 

[ExcelChampion]

Barry, I highly respect your opinion and take it into consideration. Your example is food for thought.

I am very interested in others' opinions as well...

 

[barry houdini]

I am very interested in others' opinions as well...

Yeah this is more a converstaion than a discussion at the moment.....

My last word on this (for now at least).....here's an example of a DATEDIF result which, I would suggest, might back up your view more than mine expressed above

A1 contains 30-Nov-2006
B1 contains 1-Mar-2007

=DATEDIF(A1,B1,"md")

returns -1

 

[fairwinds]

I am very interested in others' opinions as well...

FWIW

As DATEDIF does not accept the local date format as e.g. TEXT I find it not logic and it causes confusion in local versions.

E.g.

=TEXT(A2,"y") must be =TEXT(A2,"å") with swedish date settings while =DATEDIF(A1,A2,"y") must remain as is.

 

[Greg Truby]

Maybe I'm just a bit slower'n the average bear, but I'm not seeing any bug.

DateDif() is truncating the remainders of the month. DateDif("11/30/xx","12/28/xx","m") returns 0, and ...1/28/xx+1... returns 1 month, so why wouldn't ...2/28/xx+1... return 2 months?

Same w/ Barry's example (i.e. I don't see the "bug"). You're asking Excel to tell you the date for "February 31^st". Wouldn't that be "March 3^rd"?

^edit Oh. Just saw Barry's second post about crossing February using "md" for third arg. Jan-31 to Mar-1 returns a -2. I would call that a bit of a bug. Alas, I can't change my vote... Now, the real question is, "will I remember this should I opt to use DateDif() for something two years from now"? _/edit

 

[ExcelChampion]

Maybe I just can't see it, but if the last day of November to the last day of January is 2 months, then is it not logical to say that the last day of November to the last day of February is 3 months? But, according to Datedif, it is not 3 months, it is 2 months! That just seems like a bug to me.

But, I'm really trying to see it the other way. It's like an optical illusion to me...if I stare at it long enough maybe it'll pop out at me

 

[Greg Truby]

...but if the last day of November to the last day of January is 2 months, then is it not logical to say that the last day of November to the last day of February is 3 months?...
~xlchamp

Based on observation, don't look ta me like "Last day of the month" has got anything to do with how it works. It's looking at "have I gone past the same day in the following month?". Eg: DateDif("10/31/xx","11/30/xx","m") returns 0.

 

[ExcelChampion]

Based on observation, don't look ta me like "Last day of the month" has got anything to do with how it works. It's looking at "have I gone past the same day in the following month?". Eg: DateDif("10/31/xx","11/30/xx","m") returns 0.

This exactly why I think it's buggy. A "month" is not determined by an absolute number of days. A "month" is determined by how many days are in a predefined time span.

Ugh, I guess I just need to accept it...

 

[Scott Huish]

This exactly why I think it's buggy. A "month" is not determined by an absolute number of days.

I don't think you can determine a "month" by an absolute number of days because not all months have the same number of days.