# Calculate Difference between months

#### sujittalukde

##### Well-known Member
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.

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### DonkeyOte

##### MrExcel MVP
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

##### MrExcel MVP
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")

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,669
Messages
5,838,687
Members
430,563
Latest member
Raeyven

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back