Calculating numerical month (1 to 12) from text date
 

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?

Calculating numerical month (1 to 12) from text date
 

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

On Oct 29, 8:49*am, Claire_S
wrote:
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?

Calculating numerical month (1 to 12) from text date
 

=MONTH(DATEVALUE("1 "&A1&" 2000"))
--
David Biddulph

"Claire_S" wrote in message
...
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?

Calculating numerical month (1 to 12) from text date
 

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.
--
Gary''s Student - gsnu200908


"Claire_S" wrote:

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?

Calculating numerical month (1 to 12) from text date
 

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

Gary''s Student wrote:
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.

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?

Calculating numerical month (1 to 12) from text date
 

On Oct 29, 8:44*pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
But wouldn't that get screwed up if the next filename was 8 May 2007.xls?
--
David Biddulph



Gary''s Student wrote:
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.

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?- Hide quoted text -

- Show quoted text -

With text month in A1

=MONTH(A1&1)