Month name to number of months YTD

[Guest]

I have a sheet with lots of year to date data by month, the last column on
the right, which is AN, contains the abbreviated name of the month for each
row, formatted as m to show JAN, FEB,etc. This sheet is updated daily for
year to date info. I am trying to figure out an average month to date, so am
trying to figure out how to assign my JAN(in column AN) as the number of
months YTD. So for instance in this months column AN, it displays JUL next
to each row of information. in AO, I would like it to show 7 months current
YTD, then next month show 8 and so on. That way I can use that cell to
figure my average. How would I write a formula to do that?

 

[Harlan Grove]

. . . So for instance in this months column AN, it displays JUL next
to each row of information. in AO, I would like it to show 7 months
current YTD, then next month show 8 and so on. . . .

....

One way,

=MATCH(AN2,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug",
"Sep","Oct","Nov","Dec"},0)

 

[Harlan Grove]

One way,

=MATCH(AN2,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug",
"Sep","Oct","Nov","Dec"},0)

And a simpler way,

=MONTH(AN2&"-1")

 

[Guest]

That worked perfect!!! Thank you thank you!!!

....

One way,

=MATCH(AN2,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug",
"Sep","Oct","Nov","Dec"},0)

 

[Guest]

Always love the shorter ways
Thanks!!!

And a simpler way,

=MONTH(AN2&"-1")

 

[Sandy Mann]

Harlan,

I know that you will have a reason for it so can I ask you why:

=MONTH(AN2&"-1")


When =MONTH(AN2&"0"), (or any other number from -99 to 99), seems to do the
same thing?


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Harlan Grove]

I know that you will have a reason for it so can I ask you why:

=MONTH(AN2&"-1")

When =MONTH(AN2&"0"), (or any other number from -99 to 99), seems
to do the same thing?

....

And they don't need to be quoted. Wouldn't have occurred to me that
Excel would treat, e.g., "JUL0" as a valid date string.

 

[Sandy Mann]

Thank you Harlan.

The earliest reference that I can find in Google to it being used is Aladin
Akyurek in February 2002 but I find that the new Google format is not a good
as it used to be.

--
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk