Excel Month difference count?

Django x2 · 17 Apr 2011 at 23:17

Hi guys,

I have a spreadsheet with 3 columns. One is a start month, the other an end month. The 3rd needs to show the difference in the number of months as a figure. So, say col 1 had April, Col 2 had June, col3 shows "2"

Dooable?

Souleh · 18 Apr 2011 at 00:15

You could just make a table referencing months to numbers, 1 -> Jan etc

Then a vlookup(<col 2>, <table ref>, 2, FALSE)-vlookup(<col 1>, <same table ref>, 2, FALSE)

Jobs a good'un?

AHarvey · 18 Apr 2011 at 02:28

this any good:

http://www.theexceladdict.com/_t/t040303.htm

??

seems it might need a full date, not got excel on my netbook so can't check for you.

kkelly · 18 Apr 2011 at 10:53

=SUM((MONTH(1&J3))-(MONTH(1&I3)))

GravyMonster · 18 Apr 2011 at 11:00

^ Problems occur when spanning years though. Try December and January.

OP, is there no way to include a year with the month?

james.miller · 18 Apr 2011 at 11:03

in that case you'd just check if the first month was > the second month and change the calculation accordingly. Well, that's what id do

GravyMonster · 18 Apr 2011 at 11:14

What if it was a two year gap?

I know I'm being picky, but without more detail from the OP it's hard to give a definitive solution.

james.miller · 18 Apr 2011 at 11:20

assuming its not rolling 12 months then yes, you'd need the year information in there as well

kkelly · 18 Apr 2011 at 12:14

He never said anything about changing years

Django x2 · 18 Apr 2011 at 12:28

Yeah, not spanning years, just 12 months from jan to dec in any given year

Goofball · 18 Apr 2011 at 12:55

Excel does have a DateDiff function, but for some reason only available to Macros :/

You can add this to your workbook, and get access to it through a function:

Code:

Function DateDifference(interval as String, d1 As Date, d2 As Date)
        DateDifference = DateDiff(interval, d1, d2)
End Function