#### Nitika.Kaul

##### New Member

Pls help me in resolving this issue as it is related to my daily work activity.

- Thread starter Nitika.Kaul
- Start date

Pls help me in resolving this issue as it is related to my daily work activity.

Can you check the following link , and see whether you are using it properly ?

http://www.cpearson.com/excel/datedif.aspx

Narayan

I've been using this function from last 6 yrs, but for the first time i've faced this error. Specifying examples below to clarify my doubt.

Eg 1:

Start date: 15 Nov 2009

End date: 12 Jan 2011

function:datedif(start_date, End_date, "md")

Result: 28....Correct

Eg 2:

Start date: 15 Nov 2010

End date: 12 Jan 2012

function:datedif(start_date, End_date, "md")

Result: 141????

Pls help. Thanks

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.

Narayan

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)

http://www.2shared.com/file/LGdsfDmr/Excel_2007__for_NitikaKaul_at_.html

Regards!

Check out your worksheet by entering the following dates :

start date = 12/31/1999 , end date = 1/30/2004

and see what =DATEDIF(start date , end date) returns.

If it returns a consistent value , then I can only conclude that my copy of Excel is corrupted.

Narayan

Hi!

Checked, added at the end, row 426: 30 days.

http://www.2shared.com/file/9_m3zVf2/Excel_2007__for_NitikaKaul_at_.html

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?

Hope we'll find an explanation.

Regards!

@Nikita.Kaul

Hi!

Hope it works for you, otherwise please tell us.

Regards!

I tried changing my regional settings to Spanish (Argentina) , but things did not improve ; I still got 143 !

Narayan

I don't know what to say, useful at least. Except asking the obvious:

When you downloaded my file, in C426 (the formula value) you get 143 and in D426 (the previously copied value) 30?

I can't believe it, if you didn't tell me, of course.

I'm sure (or hope so) you don't need it, but here's a print screen of the working workbook, with C426 selected.

http://www.2shared.com/photo/DndZ6Suo/Excel_2007__for_NitikaKaul_at_.html

Regards!

Thanks for the screen image. Of course , I have no explanations. The best thing is not to use an undocumented function !

Narayan

I found this link:

http://blog.contextures.com/archives/2010/02/01/calculating-ages-in-excel/

I followed the sublinks, I tried many examples said to be incorrect, and with many of them I get a correct value, just for two I get the same wrong values.

So... no more time to this function.

Instead use this:

=DIA(B1)-DIA(A1)+SI(DIA(A1)>DIA(B1);DIA(FIN.MES(A1;0));0) -----> in english: =DAY(B1)-DAY(A1)+IF(DAY(A1)>DAY(B1),DAY(EOMONTH(A1,0)),0)

It's really astonishing that Redmond guys can't document/support such a silly (I'd say stupid) implementation of that function, like this above!

Check the last book:

http://www.2shared.com/file/36ajum6e/Excel_2007__for_NitikaKaul_at_.html

Regards!

EDITED 24/08/2012:

Hi!

New download link since 2shared became erratically available:

https://dl.dropbox.com/u/60558749/Excel%202007%20%28for%20Nitika.Kaul%20at%20chandoo.org%29.xlsx

Regards!

@Narayank: its really surprising to see that the error is till 14 Jan 2012 only. Wonder why!!! When i change the date to 16 Jan 2012, the formula gets correct. I must say great Observation.

@SirJ: Thanks for the bypass formula

Hi!

I wrote:

A1: 31/12/1999

A2: 30/01/2004

A3: either with DATEDIF with "md" or with my upper formulas I get a value of 30 (which is right)

A4: =A2-A1

I do what you suggested and I can't manage to find out a format that's suitable for getting a 30 value in A4. Maybe there's a very simple way, that I can't find out, is there?

Regards!

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.

Regards!