# Formula for spanned months

#### yee388

##### Well-known Member
I have two columns of dates:

1/1/06 --- 2/10/06
12/26/05 --- 3/25/06
8/27/05 --- 9/12/05

I want a formula for the number of months spanned.

so, for the above dates,

Jan, Feb = 2
Dec, Jan, Feb, Mar = 4
Aug, Sept = 2

Thanks!

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### colbymack

##### Active Member
Does this work for you:
=((YEAR(E5)-YEAR(D5))*12)+MONTH(E5)-MONTH(D5)

Where E5 has the later date, and D5 has the earlier date.

#### colbymack

##### Active Member
Sorry - you will want to add 1 at the end of that formula. I forgot that Dec to Mar =4, and not 3 (or 3.something)

#### barry houdini

##### MrExcel MVP
perhaps

=IF(LEN(A1)*LEN(B1),DATEDIF(A1,B1,"m")+1+(DAY(A1)>DAY(B1)),"")

#### yee388

##### Well-known Member
Perfect, colbymack. I love it when it's just that simple.

Barry, I haven't tried yours, but thanks for the effort.

Replies
2
Views
268
Replies
10
Views
569
Replies
2
Views
282
Replies
3
Views
309
Replies
1
Views
187

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,163,802
Messages
5,833,755
Members
430,230
Latest member
Doug G

### 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.

### Which adblocker are you using?

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

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