Convert to date using Text to Columns

[lezawang]

Hi
I have dates like the one below. I saw video on how to covert them using text2columns. So I used it and went to delimiter (unchecked all) then next page, I select Date, then select different date formats.
I got not consistent result. The only result was correct when I select YMD format. I have a feeling YMD is only one accepted because my date is for example 20211116 (which is yyyymmdd) so in Text 2 columns
I have to select YMD format to match my date. Am I right? By the way my computer/excel date is DD/MM/YYYY.

aadvanced - all.xlsm
	A	B	C	D	E
4	Date	MDY	DMY	YMD	YDM
5	20210111	#######	20210111	11/01/2021	01/11/2021
6	20210206	#######	20210206	06/02/2021	02/06/2021
7	20211011	#######	20211011	11/10/2021	10/11/2021
8	20210809	#######	20210809	09/08/2021	08/09/2021
9	20210522	#######	20210522	22/05/2021	#################
10	20211116	#######	20211116	16/11/2021	#################
text2no

 

[Rick Rothstein]

Text To Columns doesn't convert dates to a specific format, rather, it recognizes if a value can be construed to match the specified date ordering and, if so, converts that value to a real date.

 

[Dave Patton]

Use the formula or Text to Columns "YMD"
Format the dates to your preference

Date and Time 2021.xlsm
	A	B	C	D	E
1	Date Text	Date		Date Text	Text to Columns
2	20210111	11-Jan-21		20210111	11-Jan-21
3	20210206	6-Feb-21		20210206	6-Feb-21
4	20211011	11-Oct-21		20211011	11-Oct-21
5	20210809	9-Aug-21		20210809	9-Aug-21
6	20210522	22-May-21		20210522	22-May-21
7	20211116	16-Nov-21		20211116	16-Nov-21
6a

Cell Formulas
Range	Formula
B2:B7	B2	=--TEXT(A2,"0000-00-00")