Month() function doesn't work to convert January to 1

[dngandbored]

Hi there, I tried to use the month() function to convert month (January, February etc) to numeric format in Excel. It doesn't work. I don't know what went wrong. Please see my screenshot below
I typed "March" in C4 and try to convert to "3" by using month( ) but I couldn't please help!!

 

[dngandbored]

In the image from post #4 it looks like you have incorrect quote marks - they need to be the 'vertical' quote marks not the sloped ones.

View attachment 51187

However, that does not explain the failure of the original formula that has no quote marks in it.

I tried again but still failed..

include space before 1 also failed..

 

[MARK858]

You still haven't got a space in front of the 1 in that formula in the image attached

 

[dngandbored]

You still haven't got a space in front of the 1 in that formula

no?

I copied your formula and pasted to my excel it shows #VALUE!, didn't change anything. Above is what I hardwired...

 

[MARK858]

What language is your Excel in?

 

[GWteB]

I would like to add the MONTH worksheet function is sensitive to locale.
With my currrent regional settings I get 4 valid results and 8 #VALUE! errors.

The OP seems to have other regional settings:

 

[dngandbored]

What language is your Excel in?

It's in Chinese

 

[dngandbored]

I would like to add the MONTH worksheet function is sensitive to locale.
With my currrent regional settings I get 4 valid results and 8 #VALUE! errors.

The OP seems to have other regional settings:

View attachment 51192

I changed my language setting to English and this still doesn't work... I have no idea now

 

[MARK858]

With your Windows regional date settings set to English and your Excel date settings set to English, what do you see when you download the file in the link below?

Boxtest.xlsx | Powered by Box

app.box.com

 

[Peter_SSs]

I don't know a lot about dealing with dates with different regional settings but perhaps this work-around might work?

21 11 14.xlsm
	C	D
4	March	3
Month

Cell Formulas
Range	Formula
D4	D4	=MATCH(LEFT(C4,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0)

 

[Peter_SSs]

.. or a bit shorter

21 11 14.xlsm
	C	D
4	March	3
Month

Cell Formulas
Range	Formula
D4	D4	=SEARCH(LEFT(C4,3),"..JanFebMarAprMayJunJulAugSepOctNovDec")/3