How to extract month number from month name

I have a workbook that have multiple workshhets in it. The worksheets are
called January, February, March ..... (Please note thare are a couple of
worksheets which are not month related). Within each month sheet I have a
column that has a reference number 01 for Jan, 02 for Feb, 03 for March... At
the moment the reference numbers are typed in manually.

Is there a way where I can extract the month number from the month name and
automatically insert it into the reference number column?

Any help would be appreciated.

This formula will extract the month name from the sheet tab and calculate
its index

=MONTH(DAY(TODAY())&"-"&MID(CELL("filename",A1),FIND("]",CELL("filename",A1)
)+1,255)&"-"&YEAR(TODAY()))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"PM" <PM@discussions.microsoft.com> wrote in message
news:AE7B5651-76C9-4D32-B238-5E17D0C7F232@microsoft.com...
> I have a workbook that have multiple workshhets in it. The worksheets are
> called January, February, March ..... (Please note thare are a couple of
> worksheets which are not month related). Within each month sheet I have a
> column that has a reference number 01 for Jan, 02 for Feb, 03 for March...
At
> the moment the reference numbers are typed in manually.
>
> Is there a way where I can extract the month number from the month name
and
> automatically insert it into the reference number column?
>
> Any help would be appreciated.

Bob,

Many thanks for you time and formula, works a treat.

Pank

"Bob Phillips" wrote:

> This formula will extract the month name from the sheet tab and calculate
> its index
>
> =MONTH(DAY(TODAY())&"-"&MID(CELL("filename",A1),FIND("]",CELL("filename",A1)
> )+1,255)&"-"&YEAR(TODAY()))
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "PM" <PM@discussions.microsoft.com> wrote in message
> news:AE7B5651-76C9-4D32-B238-5E17D0C7F232@microsoft.com...
> > I have a workbook that have multiple workshhets in it. The worksheets are
> > called January, February, March ..... (Please note thare are a couple of
> > worksheets which are not month related). Within each month sheet I have a
> > column that has a reference number 01 for Jan, 02 for Feb, 03 for March...
> At
> > the moment the reference numbers are typed in manually.
> >
> > Is there a way where I can extract the month number from the month name
> and
> > automatically insert it into the reference number column?
> >
> > Any help would be appreciated.
>
>
>