# Calculating month difference only

#### rrmando

##### Board Regular
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

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
Helo me. I figured it out.

=DATEDIF(G2,P2,"YM")

Good job me....good job.

#### babycody

##### Well-known Member
Try this. HTH
Book5
ABCD
13/14/20054/14/200613
24/14/20063/14/200513
3
4
5
Sheet1

#### rrmando

##### Board Regular
Thanks for the reply babycody.

#### rrmando

##### Board Regular

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
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
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
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)

Replies
6
Views
44
Replies
0
Views
52
Replies
1
Views
137
Replies
6
Views
88
Replies
0
Views
58

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