# DATEDIF



## ExcelChampion (Feb 25, 2007)

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 (Feb 25, 2007)

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 (Feb 25, 2007)

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 (Feb 25, 2007)

> 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 (Feb 26, 2007)

> 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 (Feb 26, 2007)

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<sup>st</sup>".  Wouldn't that be "March 3<sup>rd</sup>"?

<sup>edit</sup> 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"? <sub>/edit</sub>


----------



## ExcelChampion (Feb 26, 2007)

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 (Feb 27, 2007)

> ...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 (Feb 27, 2007)

> 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 (Mar 1, 2007)

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


----------



## ExcelChampion (Mar 1, 2007)

Right!  That's exactly what I'm saying.  Not all months have the same number of days...but in my above examples, DATEDIF is not accounting for the different number of days.  If it is logical that from Nov 30 to Jan 31 is 2 months, then it *should* be logical that Nov 30 to Feb 28 is 3 months.

This is why I think it is buggy.


----------

