Date conversion

[Dizzy0702]

Hi all,

I'm having trouble converting a date format in an old legacy report where date is in the format "1st Jan 22" which causes problems ordering the data in pivot tables. Does anyone know a way to convert this to a proper excel date format?

TIA

 

[etaf]

is it being shown in the data as text
if you right click on the date field and change to format General, does it change to a number

does the date include the other rd th ie.
24th Jan 22 ,
3rd Jan 22

so may need to use a combination of Left/Right/Mid/find etc

=DATEVALUE(SUBSTITUTE(A1,MID(A1,FIND(" ",A1)-2,2),""))

 

[etaf]

Book1
	A	B
1	1st jan 22	1/1/22
2	2nd jan 22	1/2/22
3	3rd jan 22	1/3/22
4	14th jan 22	1/14/22
5	5th jan 22	1/5/22
6	31st jan 22	1/31/22
Sheet1

Cell Formulas
Range	Formula
B1:B6	B1	=DATEVALUE(SUBSTITUTE(A1,MID(A1,FIND(" ",A1)-2,2),""))

 

[Dizzy0702]

Book1
	A	B
1	1st jan 22	1/1/22
2	2nd jan 22	1/2/22
3	3rd jan 22	1/3/22
4	14th jan 22	1/14/22
5	5th jan 22	1/5/22
6	31st jan 22	1/31/22
Sheet1

Cell Formulas
Range	Formula
B1:B6	B1	=DATEVALUE(SUBSTITUTE(A1,MID(A1,FIND(" ",A1)-2,2),""))

That's great thank you!!

 

[etaf]

you are welcome