• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel 2007

N

Nitika.Kaul

New Member
Excel 2007 is calculating wrong no. of days where the start date is of 2011 & end date of 2012. Function: datedif(start_date, end_date, "md")+1.

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


Firstly, Welcome to the Chandoo.org Forums


Can you be more specific about what dates it isn't working for ?
 
Hi Nitika ,


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


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


Narayan
 
Thanks Hui :)


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
 
Hi Nitika ,


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
 
Hi, Nitika.Kaul!

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!
 
Hi ,


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
 
@NARAYANK991

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!
 
Hi ,


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


Narayan
 
@NARAYANK991

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!
 
Hi ,


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


Narayan
 
@Nikita.Kaul, NARAYANK991


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!
 
Thanks a lot Narayank & SirJB7 :)

@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 :)
 
@Nitika.Kaul


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


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!
 
@SirJB7


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


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!
 
You must log in or register to reply here.
Back
Top