I used a start date of 15-Nov-2010 , and checked the DATEDIF function result for all dates thereafter , starting with 16-Nov-2010 ; it starts with 1 , goes up to 29 , and then resets to 0 , goes up to 30 and repeats this behaviour. When the dates are between 1-Jan-2012 and 14-Jan-2012 , the return values are from 130 to 143.
Again , 15-Jan-2012 and after , the behaviour is consistent , varying between 0 and a maximum of 30 each month ( some months the maximum value is 27 , others 28 , 29 and 30 ). But the inconsistent behaviour repeats from 1-Jan-2016 and 14-Jan-2016 , when the return values are again between 130 and 143.
Every leap year results in this inconsistent behaviour.
Of course , other combinations of start and end dates give other results !
Since this is an undocumented and unsupported function , I think you have to live with this inconsistent behaviour.
Making efforts to remember older ages of Excel 2000 (I'm using Excel 2010), last version where it's documented -as stated in Hui's post link-, I'm sure I never got problems with it, although I didn't use it every day.
So I decided to try your examples and in both cases I got 28 (rows 58 and 423).
So I decided then to perform NARAYANK991's test and I always got a number from 0 to 30.
For me, it remains being an obscure, indocumented but coherent function.
Check this file, columns A:D (A starting date, B ending date, C datedif function, D value copied from C just for checking)
I don't think your copy (I guess you meant original of course, isn't it? ;-o) ...) is corrupt. I believe it might be something about regional configuration or something like that. And even in that case, it would be more probable that I have problems with my spanish Excel and dd/mm/yyyy configuration and not you (I assume you have english Excel and mm/dd/yyyy date format).
Can you set up your system for a while to English UK, or better indeed Spanish ARG or Spain, and check again my file?
reading your original posting, why not just use cell2 minus cell1 and change the format of the calculated cell to general to show the number? Why use Datedif? You don't need make it a complicated matter.
First of all, I don't understand why the answer has to be 30. I read the FIRST posting and it didn't specific why the special need for datedif. It only said he's trying to find out "number of days" between two dates.
Second, when I said format of the cell to general is because (may be it has to do with US version of Excel) if I don't do that, using your example, I will get 30/1/1904 as in Jan 30th, year 1904.
By changing the "format" of the cell to "General" it will convert into 1491 days. May be I had a brain tumor.
p.s. i realize your date format is european one where the default is dd/mm/yyyy. in the US the default is mm/dd/yyyy. so my A1 would be: 12/31/1999 and A2 01/30/2004.
In this first post at topic opening Nitika.Kaul specifies the use of DATEDIF function with "md" parameter. In our interchange of experiences and find outs with NARAYANK991, I posted links to a worksheet that I uploaded, where there were a lots of pairs of dates for testing purposes.
In your first today post you say x2-x1 and use general format. I thought "what if the solution was as simple as that and we've been trying to re-invent the wheel?". So I opened the uploaded workbook, added a clean sheet, and tested what you suggested with the last correct values on that workbook (that happened to be those I posted in Latin American format). As I found problems, I wrote to you.
I still don't know how I can get the equivalent to the right working DATEDIF with "md" or my bypasing upper formula. Just FYI I stated that the value should be 30. Do you know? Thanks.