Calculating month difference only

rrmando

Board Regular
Joined
Dec 23, 2004
Messages
212
Hello. I need to calculate the difference of months only. Unfortunately, the data we have been provided is not in the same format.

We have column G containing the dates formatted:

3/10/2006

and we have column P containing only the number of months, i.e.:

9

I tried =MONTH(G2)-MONTH(P2), but if the month in column G is greater than the month in column P, I need to count FROM G to P. For example, assume:

G = 11/31/1991 and P = 09, the difference I need to calculate is FROM 11 to 09, which would be 10 months. The formula above gives me -2 months. I need it to calculate the difference in months going forward if that makes any sense. I also need the result to be in NUMBER format (which the MONTH formula gives). We will use that result to multiply with a rate.

I know our formatting is weird, but this is the data we have to work with and it's a couple of thousand rows. Thank you.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Oh, just noticed babycody, I was looking for the difference in MONTHS only. I couldn't figure out how to ignore the years. Thanks again.
 
Upvote 0
Your welcome. You almost had it, but DATEDIF needs the earlier date first. The formula I gave you will work both ways like you asked.
 
Upvote 0
I'm not sure it is relevant to the original problem, but another way to handle DATEDIF if it is unknown which date is going to be the earlier is:
=DATEDIF(MIN(A1:B1),MAX(A1:B1),"m")
 
Upvote 0
Helo me. I figured it out.

=DATEDIF(G2,P2,"YM")

Good job me....good job.

Is the day relevant or just the month? If you use the above formula and G2 contains 11/30/2006 and P2 9/30/2007 you'll get 10 but if you change P2 by 1 day to 9/29/2007 you'll get 9

If you always want to get 10 then

=MOD(MONTH(P2)-MONTH(G2),12)
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

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
Back
Top