Calculating numerical month (1 to 12) from text date

[Claire_S]

I have extracted the month from the worksheet name using the CELL function to
give me the month name as text. Is there a way I can convert this to a
numerical month?

 

[Pete_UK]

If you list those months in order in a block of cells somewhere, eg
starting in X1:

January
February
March

etc., then you can use this:

=MATCH(TRIM(D2),X$1:X$12,0)

assuming your months are in column D. Copy down as required.

Hope this helps.

Pete

 

[David Biddulph]

=MONTH(DATEVALUE("1 "&A1&" 2000"))

 

[Gary''s Student]

Say the file name is something like 18 January 1863.xls

In B1 thru B3 enter:

=CELL("filename",A1)
=MID(B1,47,15)
=MONTH(DATEVALUE(B2))

to display:

C:\Documents and Settings\Owner\My Documents\[18 January 1963.xls]Sheet1
18 January 1963
1

This gets you the "1" for January.

 

[barry houdini]

But wouldn't that get screwed up if the next filename was 8 May 2007.xls?
--
David Biddulph

Say the file name is something like 18 January 1863.xls

In B1 thru B3 enter:

to display:

C:\Documents and Settings\Owner\My Documents\[18 January
1963.xls]Sheet1 18 January 1963
1

This gets you the "1" for January.

- Show quoted text -

With text month in A1

=MONTH(A1&1)