Calculate Difference between months

sujittalukde

Well-known Member
Joined
Jun 2, 2007
Messages
520
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.
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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
Joined
Mar 23, 2005
Messages
20,825
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")
 

Watch MrExcel Video

Forum statistics

Threads
1,122,418
Messages
5,596,028
Members
414,039
Latest member
southike

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?

Disable AdBlock

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
Top