Problem with DateValue

[sharshra]

I have to convert month in short form (Jan, Feb etc) to number. I´m using DateValue formula. It returns correct values for few months & error for few. Not sure why the results are inconsistent. Can the experts advise what is going wrong please?

Book1
	B	C
2	Jan	#VALUE!
3	Feb	2
4	Mar	3
5	Apr	#VALUE!
6	May	5
7	Jun	6
8	Jul	7
9	Aug	#VALUE!
10	Sep	9
11	Oct	10
12	Nov	11
13	Dec	#VALUE!
Sheet1

Cell Formulas
Range	Formula
C2:C13	C2	=MONTH(DATEVALUE(B2&"1"))

 

[Special-K99]

What does =LEN(B2) give you? It should be 3, anything else and there are extra characters in there, maybe ones that don't display, meaning the value in B2 is NOT "Jan" so the DATEVALUE() formula may fail.

 

[sharshra]

Length is 3 for all these. Even if I trim text & use in DateValue, it is throwing error.

Book1
	B	C	D
2	Jan	#VALUE!	3
3	Feb	2	3
4	Mar	3	3
5	Apr	#VALUE!	3
6	May	5	3
7	Jun	6	3
8	Jul	7	3
9	Aug	#VALUE!	3
10	Sep	9	3
11	Oct	10	3
12	Nov	11	3
13	Dec	#VALUE!	3
Sheet1

Cell Formulas
Range	Formula
C2:C13	C2	=MONTH(DATEVALUE(B2&"1"))
D2:D13	D2	=LEN(B2)

 

[shift-del]

Your regional settings are not suitable for english month names.
In Germany it would throw an error on Mar, May, Oct and Dec because the the month names have to be Mrz, Mai, Okt and Dez.

So, you should align the month names to the regional settings or vice versa.

 

[Special-K99]

Your regional settings are not suitable for english month names.
In Germany it would throw an error on Mar, May, Oct and Dec because the the month names have to be Mrz, Mai, Okt and Dez.

So, you should align the month names to the regional settings or vice versa.

Darn! I wish I'd thought of that! Good call!

 

[FormR]

Here's an alternative to convert the English abbreviations that is not contingent on your regional settings.

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

 

[sharshra]

Perfect Thanks a lot, FormR. Thanks to shift-del also to pointing out to right direction
You experts are the reasons for me to love this forum

 

[shift-del]

Here's an alternative to convert the English abbreviations that is not contingent on your regional settings.

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

Another option.

Mappe13
	B	C
7	Jan	1
8	Feb	2
9	Mar	3
10	Apr	4
11	May	5
12	Jun	6
13	Jul	7
14	Aug	8
15	Sep	9
16	Oct	10
17	Nov	11
18	Dec	12
Tabelle1

Cell Formulas
Range	Formula
C7:C18	C7	=ROUNDUP(SEARCH(B7,"JanFebMarAprMayJunJulAugSepOctNovDec")/3,0)

 

[sharshra]

Thanks, shift-del