Date format does not correct from third party program

[maiwarits]

Hello guys,

I got date format from third party program like in the below pictures.

2023 = years
03 = month
22 = date
01 = hours time
13 = minute
50 = secs

I have tried to change date format to Short format, Custom format. But it always show like in the Cell A1.

Could you help me to change date format and separate them with date and time into 2 cells?

Thanks,

 

[James006]

Hi,
You can test

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),RIGHT(A1,2))

and use the custom format :
dd mm yyyy hh:mm:ss

 

[Georgiboy]

Maybe this:

Book1
	A	B	C
1	20230322011350	22/03/2023	01:13:50
2	20230322011450	22/03/2023	01:14:50
3	20230322011550	22/03/2023	01:15:50
4	20230322011650	22/03/2023	01:16:50
5	20230322011750	22/03/2023	01:17:50
6	20230322011850	22/03/2023	01:18:50
7	20230322011950	22/03/2023	01:19:50
8	20230322012050	22/03/2023	01:20:50
9	20230322012150	22/03/2023	01:21:50
10	20230322012250	22/03/2023	01:22:50
11	20230322012350	22/03/2023	01:23:50
12	20230322012450	22/03/2023	01:24:50
13	20230322012550	22/03/2023	01:25:50
14	20230322012650	22/03/2023	01:26:50
15	20230322012750	22/03/2023	01:27:50
16	20230322012850	22/03/2023	01:28:50
Sheet1

Cell Formulas
Range	Formula
B1:C16	B1	=LET(d,A1:A16,HSTACK(DATEVALUE(BYROW(MID(d,{1,5,7},{4,2,2}),LAMBDA(x,TEXTJOIN("/",,x)))),TIMEVALUE(BYROW(MID(d,{9,11,13},{2,2,2}),LAMBDA(x,TEXTJOIN(":",,x))))))
Dynamic array formulas.

 

[maiwarits]

Thanks guys!

It works well.

 

[Dave Patton]

more alternatives

Date conversion.xlsm
	A			D	E
1	20230322011350			22-Mar-23	01:13:50
2	20230322011450			22-Mar-23	01:14:50
3	20230322011550			22-Mar-23	01:15:50
4	20230322012850			22-Mar-23	01:28:50
5
6	or Data TexttoColumns
7	20230322			22-Mar-23
8	20230323			23-Mar-23
9	20230324			24-Mar-23
10	20230325			25-Mar-23
11
2bb

Cell Formulas
Range	Formula
D1:D4	D1	=--TEXT(LEFT(A1,8),"0000-00-00")
E1:E4	E1	=--TEXT(RIGHT(A1,6),"00\:00\:00")