DATEDIF - # of months 2016-03-31 and 2016-06-30 = 2, but should be 3

[polarisking]

=datedif("3/31/2016","6/30/2016","m")

Any idea why?

 

[Ramesh]

The reason behind the output being 2 is as follows:

3/31/2016 - 4/30/2016 - 1 month
5/1/2016 - 6/30/2016 - 1 month

This how your Datedif function is evaluating the date values supplied..

In order to address this issue the word around is to use the formula given below:

=(YEAR(B2) - YEAR(A2))*12 + MONTH(B2) - MONTH(A2)

In the above formula, first we are subtracting the year values for the given dates and multiplying the same with 12 and then adding the month difference..

Hope the above said formula helps!!

 

[polarisking]

Thank you, Ramesh. I ended up doing exactly that, but I was curious as to why a "tried and true" formula was breaking.

3/31/2016 - 4/30/2016 - 1 month
5/1/2016 - 6/30/2016 - 1 month - Not sure why you're saying this is 1 month - May is a month, as is June. In my book, that's 2.

 

[bosco_yip]

1] =DATEDIF("3/31/2016","6/30/2016","m")

Return 2

2] Reasons maybe……..

3/31/2016 - 5/1/2016 - 1 month ( Complete calendar months between the dates)

5/1/2016 - 6/1/2016 - 1 month ( Complete calendar months between the dates)

6/1/2016 - 6/30/2016 - 0 month ( Not complete calendar months between the dates)

But,

6/1/2016 - 7/1/2016 - 1 month ( Complete calendar months between the dates)

so that,

=DATEDIF("3/31/2016","7/1/2016","m")

Return 3

Regards