How do I convert month name in month number?

[scudooder]

I want to convert a list of month abbreviations (Nov, Dec, Jan, Feb
etc...) into their respective month numbers (11, 12, 1, 2, etc).
anyone have a function or vba macro I can use

 

[Bob Phillips]

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

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ron Rosenfeld]

I want to convert a list of month abbreviations (Nov, Dec, Jan, Feb,
etc...) into their respective month numbers (11, 12, 1, 2, etc).
anyone have a function or vba macro I can use?

With the name in A1:

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


--ron

 

[Don Guillett]

You might like this where h4 has Jan or January or Feb or mar or march , etc

=MONTH(DATEVALUE(1&H4))

 

[scudooder]

thanks guys, works good

 

[Don Guillett]

glad to help

 

[Peo Sjoblom]

You can shorten that to

=MONTH(--(1&A1))

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

You might like this where h4 has Jan or January or Feb or mar or march , etc

=MONTH(DATEVALUE(1&H4))

 

[Don Guillett]

Peo,
While playing with this I found it could be further shortened to
=MONTH(1&I3)
--
Don Guillett
SalesAid Software
(e-mail address removed)

You can shorten that to

=MONTH(--(1&A1))

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Peo Sjoblom]

Nice, that means one can use a text function directly in the DATE function
to parse a text like

Apr 20 2004

using

=DATE(MID(I3,8,255),MONTH(1&LEFT(I3,3)),MID(I3,5,2))

instead of adding commas and datevalue/operator, then re-format the cell
with the formula as date,
using DATE it will be formatted correctly through the formula

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Norman Harker]

Hi Peo & Don!

Nitpickers strike again! Formatted correctly but...

With:

Apr 20 2004

=DATE(MID(I3,8,255),MONTH(1&LEFT(I3,3)),MID(I3,5,2))
Returns: 20-Apr-2004

But:

Apr 2 2004

=DATE(MID(I3,8,255),MONTH(1&LEFT(I3,3)),MID(I3,5,2))
Returns: 20-Apr-1904
Bugger!

But:

Apr 20 2004

=DATE(MID(I3,7,255),MONTH(1&LEFT(I3,3)),MID(I3,5,2))
Returns: 20-Apr-2004

And:

Apr 2 2004

=DATE(MID(I3,7,255),MONTH(1&LEFT(I3,3)),MID(I3,5,2))
Returns: 2-Apr-2004

Excel appears happy when there is a double digit day element so that
the MID is returning " 2004" rather than the "2004" returned when you
have a single digit day.