Converting month name to number

M

Mats Samson

How do I transfer the month name February in a cell to its corresponding
number 2, March to 3, etc, so I can use it in a formula in another cell?
 
R

Ron Coderre

With
A1: (a month name....eg March)

Try this
=MONTH("01-"&A17)

In my example the formula returns: 3


Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
 
R

Ron Coderre

In case it isn't obvious....

My formula should reference A1, instead of cell A17

=MONTH("01-"&A1)

Regards,

Ron
Microsoft MVP (Excel)
 
M

Mats Samson

Ron Coderre said:
With
A1: (a month name....eg March)

Try this
=MONTH("01-"&A17)
Click to expand...

Sorry Ron but I can't get that to work. A17 must be an error, right?
 
S

Sandy Mann

Just another way:

=MONTH(A1&0)

--
HTH

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
R

Rick Rothstein \(MVP - VB\)

A1: (a month name....eg March)
Try this
=MONTH("01-"&A1)

In my example the formula returns: 3
Click to expand...

Looks like you can shorten your formula a tad more; this seems to work...

=MONTH("1"&A4)

Rick
 
M

Mats Samson

Ron Coderre said:
With
A1: (a month name....eg March)

Try this
=MONTH("01-"&A17)
Click to expand...

Thanks Ron, it worked now.
I had the cell Date formatted, it should be General!
Simple and swift, nice!
Best regards
Mats
 
R

Rick Rothstein \(MVP - VB\)

A1: (a month name....eg March)
Looks like you can shorten your formula a tad more; this seems to work...

=MONTH("1"&A4)
Click to expand...

Actually, the quote marks are not needed...

=MONTH(1&A4)

Rick
 
R

Ron Coderre

You're right....by fussing with a character here and there the formula can
be shortened. However, for something this basic, I went with a fairly
intuitive, non-ambiguous, easily remembered date format that Excel would
properly translate (eg "01-March").

Regards,

Ron
Microsoft MVP (Excel)
 
Top