DateDif()

[Evagrius Ponticus]

Hi,

Can anyone help me understand why this formula gives me a result of -1?


a1 = 7/31/2012
b1 = 3/2/2013


datedif(A1,B1,"MD")

 

[mvptomlinson]

I believe it's because:

1. The "MD" qualifier is only going to tell you how many days there are between the two dates assuming they are in the same month -- LEAVING off the Years and Months part. So if you put 4/1/07 to 5/2/07 it would result in 1.

2. HOWEVER -- In your example, 7/31/2012 to 3/2/2013, while there are technically 7 complete months, there really is no 2/31/2013. My best guess is that it is calculating 3/3/2013 as the 31st day (the end of the 7th full month) and then showing you that there is -1 day "left over". Notice if you change your end date to 3/3/2013 the result becomes 0. Then 3/4/2013 = 1..etc.

It's odd, and complicated, but mostly odd. Perhaps these explanations and examples could help further explain DATEDIF: http://www.cpearson.com/excel/datedif.aspx

 

[orbea_adam]

I get 2. Adam

 

[Evagrius Ponticus]

Thanks MVP - The DATEDIF function never makes sense to me - all I am trying to do find the difference between those two dates for months and days.

Don't know how you are getting 2 Adam - I tried it again and I still get -1??

 

[Brunts]

I'm on Excel 200 and I'm getting the -1 as an outcome.

Datedif doesn't always create the outcome expected and can give a response in negative days

See the Thread titled "Bug in DATEDIF in Excel 2007?