Counting Months to get an Average

[LinLin]

Hi Everyone

I have month columns with labels which start with April 2009 through March
2010.

I have another cell which indicates the current month.

If the current month is December,
I need a formula which looks at my columns and tells me that April to
December is 8 months.

If I am in January however, I need the formula to calculate 9 months.
(Most of the date functions obviously assume your first month is January (1)
and last month is December (12))

(Or if it doesn't look at the columns, that's fine too, but I figure that
will help somehow).
I am trying to create an average function but it’s a bit trickier than I
anticipated!
I have tried some If formulas but it’s a bit beyond me.

Hope I've explained that OK - happy new year to all!

 

[Bob Phillips]

Maybe this will help

=DATEDIF(first_month_cell,check_cell,"M")

 

[Shane Devenshire]

Hi,

If you enter the current month a label "December" and the entries in the
other column are really "labels" not dates then suppose the list of months
is in A1:A12 and the current month is entered in B1:

=MATCH(TRUE,ISNUMBER(FIND(C1,A1:A12)),0)-1

This is an array formula so you must enter it by pressing Shift+Ctrl+Enter

If the entries in A1:A12 are really dates then try this

=MATCH(G1,TEXT(A1:A12,"mmmm"),0)-1

This is also array entered.

If the entries in A1:A12 and December are dates then "maybe" you can use
DATEDIF:

=DATEDIF(A1,B1,"m")

But note that all dates must be entered as the same day of the month, why?
If A1 is 4/2/2008 and B1 is 12/1/2008 your answer will be one month off
because DATEDIF with "m" only counts whole months.



If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[LinLin]

Thanks Shane and Bob - great responses.

(And sorry I think I created some confusion, December should have been "9"
not 8, I have amended the formulas where necessary, only the last formula
needed to be changed by adding +1 - apologies for that!)

Everything worked great, it will save a heap of time!

cheerio