convert 20230201000051 to date and time

[junkforhr]

Hi, I have data like below. This is just a sample and the actual data is about 20k rows.

The column with the date and time is unhelpful and I'd like 3 new columns (as per my desired result below), ideally formatted as Date, Time and Date and Time respectively.

Hoping someone can help.

Original data

20230201000051​

20230201000121​

20230201000223​

20230201000240​

20230201000308​

20230215165310​

20230215165357​

20230215165922​

20230215161358​

20230215165222​

20230215164502​

Desired Result

Original data	Date	Time	Date and Time
20230201000051​	1/02/2023​	00:00:51	01/02/2023 00:00:51
20230201000121​	1/02/2023​	00:01:21	01/02/2023 00:01:21
20230201000223​	1/02/2023​	00:02:23	01/02/2023 00:02:23
20230201000240​	1/02/2023​	00:02:40	01/02/2023 00:02:40
20230201000308​	1/02/2023​	00:03:08	01/02/2023 00:03:08
20230215165310​	15/02/2023​	16:53:10	15/02/2023 16:53:10
20230215165357​	15/02/2023​	16:53:57	15/02/2023 16:53:57
20230215165922​	15/02/2023​	16:59:22	15/02/2023 16:59:22
20230215161358​	15/02/2023​	16:13:58	15/02/2023 16:13:58
20230215165222​	15/02/2023​	16:52:22	15/02/2023 16:52:22
20230215164502​	15/02/2023​	16:45:02	15/02/2023 16:45:02

 

[awoohaw]

How about this:

mr excel questions 5.xlsm
	A	B	C	D
2	20230201000051	02/01/2023	0:00:51	02/01/2023 0:00:51
3	20230201000121	02/01/2023	0:01:21	02/01/2023 0:01:21
4	20230201000223	02/01/2023	0:02:23	02/01/2023 0:02:23
5	20230201000240	02/01/2023	0:02:40	02/01/2023 0:02:40
Sheet11

Cell Formulas
Range	Formula
B2:B5	B2	=DATE(LEFT(A2,4),MID(A2,5,2),MID(A2,7,2))
C2:C5	C2	=TIME(1*MID(A2,9,2),1*MID(A2,11,2),1*MID(A2,13,2))
D2:D5	D2	=B2+C2

 

[awoohaw]

actually you don't need the 1* in the time calculation. The problem was with zero length text character in the value as I copied your data over. So, this should work for you:

=TIME(MID(A2,9,2),MID(A2,11,2),MID(A2,13,2))

and possibly:

=TIME(MID(A2,9,2),MID(A2,11,2),RIGHT(A2,2))

 

[Phuoc]

Try this

Book1
	A	B
1	Original data	Date and Time
2	20230201000051	01/02/2023 00:00:51
3	20230201000223	01/02/2023 00:02:23
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=TEXT(A2,"0000-00-00 00\:00\:00")+0

 

[junkforhr]

How about this:

mr excel questions 5.xlsm
	A	B	C	D
2	20230201000051	02/01/2023	0:00:51	02/01/2023 0:00:51
3	20230201000121	02/01/2023	0:01:21	02/01/2023 0:01:21
4	20230201000223	02/01/2023	0:02:23	02/01/2023 0:02:23
5	20230201000240	02/01/2023	0:02:40	02/01/2023 0:02:40
Sheet11

Cell Formulas
Range	Formula
B2:B5	B2	=DATE(LEFT(A2,4),MID(A2,5,2),MID(A2,7,2))
C2:C5	C2	=TIME(1*MID(A2,9,2),1*MID(A2,11,2),1*MID(A2,13,2))
D2:D5	D2	=B2+C2

works perfectly, thanks for your assistance.

 

[awoohaw]

works perfectly, thanks for your assistance.

There are a couple good solutions here. please mark the one you fell best solves your question as the answer.