Calculate Difference between months

[sujittalukde]

In cell A1 date is 30/09/2007

In cell A2 date is 29/10/2007

In cell A3 Formula is =DATEDIF(A1,A2,"m") and the Answer is 0 and the result is correct and this also I want.



However if the date in cell A2 is changed to 30/10/2007 the result in Cell A3 comes to 1 which I want to show 0 instead of 1 ie difference between the months.



Also if the date is 02/11/2007 the result is 1 whereas I want 2.





I tried to change the formula at Cell A4 with this

=MONTH(A2)-MONTH(A1)

here the result comes correct till there is no change of year. If the date in cell A2 is changed to 20/01/2008 the result changed to -8 whereas I want the result to show 4 as Answer.



What adjustment has to be made in the formula to get the difference between the month and suitably adjust for year change.

 

[DonkeyOte]

your examples are inconsistent

why would 30/10/07 vs 30/09/07 = 0 when 02/11/07 vs 30/09/07 = 2
if you're basing it on the month being different surely ANY date in October 07 equates to 1 (not 0) ?

 

[barry houdini]

I agree with lasw10, your examples don't seem to be consistent.

If the formula

=MONTH(A2)-MONTH(A1)

always gives the correct result except when the year changes then that sounds like you're just calculating the difference between months irrespective of the day, e.g. any day in September to any day in November is 2 months [although that's contradicted by some of your other examples]. If that's the case then try

=DATEDIF(A1-DAY(A1)+1,A2,"m")

If, however, the following applies.....

....A1 is always a month end date and month count only increments when A2 hits another month end date, e.g. 30th Sept 2008 to 30th Oct 2008 =0 but 30th Sept 2008 to 31st Oct 2008 =1 [30th Sept 2008 to 2 Nov 2008 is still =1] then try

=DATEDIF(A1+1,A2+1,"m")