Datevalue problems

[Bandito1]

Hello,

Im using the formula; =TEXT(DATEVALUE(1&B2);"Mmm")&B1 to get the format jan2023 from 2 cells that contain januari and 2023.
Now i want to turn it to english; but when the cell contains January i get an #value error.

Also i want that it turn into Jan23 instead of jan2023.

Someone knows what i going wrong when i start using English months and how to lose "20" from 2023

Book1
	A	B
1		2023
2	jan2023	Januari
3	#VALUE!	January
Sheet1

Cell Formulas
Range	Formula
A2	A2	=TEXT(DATEVALUE(1&B2),"Mmm")&B1
A3	A3	=TEXT(DATEVALUE(1&B3),"Mmm")&B1

 

[Dave Patton]

Regional settings on my system dd-mm-yyyy

T202210a.xlsm
	A	B	C	D
1		2023
2	Jan23	January
3	Jan23	Jan
4
5	Jan23	January	format mmmyy
6	Jan23	Jan	format mmmyy
7
2c

Cell Formulas
Range	Formula
A2:A3	A2	=TEXT(DATEVALUE(1&LEFT(B2,3)&$B$1),"Mmmyy")
A5:A6	A5	=--(1&LEFT(B5,3)&$B$1)

T202210a.xlsm
	A	B	C	D
1		2023
2	Jan2023	January
3	Jan2023	Jan
4
5	Jan2023	January	format mmmyyyy
6	Jan2023	Jan	format mmmyyyy
7
2c

Cell Formulas
Range	Formula
A2:A3	A2	=TEXT(DATEVALUE(1&LEFT(B2,3)),"Mmm")&$B$1
A5:A6	A5	=--(1&LEFT(B5,3)&$B$1)

 

[MARK858]

Someone knows what i going wrong when i start using English months and how to lose "20" from 2023

Because your regional date settings aren't in the English format (see column C in my setup below as mine are in English format).
Is there any reason you can't just use the formula in column A below (text date) or the formulas in A5:A6 in Dave's post formatted as mmmyy (real date)?

Book1.xlsb
	A	B	C
1		2023
2	Jan23	Januari	#VALUE!
3	Jan23	January	Jan2023
Sheet1

Cell Formulas
Range	Formula
A2:A3	A2	=LEFT(B2,3)&RIGHT($B$1,2)
C2	C2	=TEXT(DATEVALUE(1&B2),"Mmm")&B1
C3	C3	=TEXT(DATEVALUE(1&B3),"Mmm")&B1

 

[Bandito1]

It now looks like this;

Book1
	A	B
1		2023
2	janyy	January
3	janyy	Jan
4
5	jan23	January
6	jan23	Jan
Sheet1

Cell Formulas
Range	Formula
A2:A3	A2	=TEXT(DATEVALUE(1&LEFT(B2,3)&$B$1),"Mmmyy")
A5:A6	A5	=--(1&LEFT(B5,3)&$B$1)

A5 is almost right; can the J of Jan be with a capital J ?
Changed cell property to custom and then [$-nl-NL]mmmjj;@

 

[Dave Patton]

You stated "Changed cell property to custom and then [$-nl-NL]mmmjj;@"

I do not have information on your systems Regional Settings or the details of Custom number formats in your region.
I suggested Custom Number format of mmmyy. How do dates normally show on your system?

You can try =PROPER(TEXT(DATEVALUE(1&LEFT(B2,3)&$B$1),"mmmyy")). I cannot test this suggestion since I do not have the same setup as you have.

or TEXT result

T202210a.xlsm
	A	B	C
1		2023
2		January	Jan23
3		Jan	Jan23
4		january	Jan23
5		jan	Jan23
6
2c

Cell Formulas
Range	Formula
C2:C3	C2	=LEFT(B2,3)&RIGHT($B$1,2)
C4:C5	C4	=PROPER(LEFT(B4,3)&RIGHT($B$1,2))