Formula for spanned months

yee388

Well-known Member
Joined
Mar 7, 2004
Messages
1,374
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!
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

colbymack

Active Member
Joined
Jul 14, 2005
Messages
333
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
Joined
Jul 14, 2005
Messages
333
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)
 

yee388

Well-known Member
Joined
Mar 7, 2004
Messages
1,374
Perfect, colbymack. I love it when it's just that simple.

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

Watch MrExcel Video

Forum statistics

Threads
1,118,436
Messages
5,572,090
Members
412,441
Latest member
kelethymos
Top