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.
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

rrmando

Board Regular
Joined
Dec 23, 2004
Messages
212
Helo me. I figured it out.

=DATEDIF(G2,P2,"YM")

Good job me....good job.
 

rrmando

Board Regular
Joined
Dec 23, 2004
Messages
212

ADVERTISEMENT

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.
 

babycody

Well-known Member
Joined
Jul 8, 2003
Messages
1,395
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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,837
Office Version
  1. 365
Platform
  1. Windows
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")
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,113,992
Messages
5,545,373
Members
410,679
Latest member
rolandbianco
Top