Convert Date & Time to correct format

[thelastpsycho]

I have this data collection that containing a date (string) which i believe in wrong format. the format as follows:

"02-03-2020 03:07 +07:00" -> "dd-mm-yyy h:i GMT"

I would like to convert that into format that readable as date & time format, something like "03/02/2020 03:07:00 AM" and my GMT is +8. so the time will be "04:07:00 AM"

How can I achieve that? is there any setting in excel or I have to use formula?

Thank you very much.

Book1
	A	B
1	Created At	Date Format
2	02-03-2020 03:07 +07:00	??
3	02-03-2020 03:10 +07:00	??
4	02-03-2020 03:18 +07:00	??
5	02-03-2020 03:22 +07:00	??
6	08-08-2020 23:59 +07:00	??
7	21-09-2020 08:56 +07:00	??
8	23-09-2020 13:27 +07:00	??
9	02-01-2021 23:22 +07:00	??
10	22-02-2021 12:40 +07:00	??
11	01-03-2021 19:09 +07:00	??
12	09-04-2021 00:21 +07:00	??
13	13-05-2021 22:59 +07:00	??
14	18-05-2021 04:23 +07:00	??
15	02-08-2021 20:47 +07:00	??
16	14-08-2021 07:00 +07:00	??
17	19-08-2021 19:20 +07:00	??
18	19-08-2021 19:33 +07:00	??
19	19-08-2021 19:52 +07:00	??
20	08-09-2021 12:40 +07:00	??
21	08-09-2021 13:05 +07:00	??
22	08-09-2021 20:49 +07:00	??
23	09-09-2021 22:39 +07:00	??
24	10-09-2021 07:00 +07:00	??
25	10-09-2021 12:35 +07:00	??
26	10-09-2021 23:00 +07:00	??
27	13-09-2021 10:55 +07:00	??
28	14-09-2021 07:00 +07:00	??
Sheet1

 

[Zot]

Book1.xlsx
	A	B
1	Created At	Date Format
2	02-03-2020 03:07 +07:00	03/02/2020 04:07 AM
3	02-03-2020 03:10 +07:00	03/02/2020 04:00 AM
4	02-03-2020 03:18 +07:00	03/02/2020 04:08 AM
5	02-03-2020 03:22 +07:00	03/02/2020 04:02 AM
6	08-08-2020 23:59 +07:00	08/08/2020 12:09 AM
7	21-09-2020 08:56 +07:00	09/21/2020 09:06 AM
8	23-09-2020 13:27 +07:00	09/23/2020 02:07 PM
9	02-01-2021 23:22 +07:00	01/02/2021 12:02 AM
10	22-02-2021 12:40 +07:00	02/22/2021 01:00 PM
11	01-03-2021 19:09 +07:00	03/01/2021 08:09 PM
12	09-04-2021 00:21 +07:00	04/09/2021 01:01 AM
13	13-05-2021 22:59 +07:00	05/13/2021 11:09 PM
14	18-05-2021 04:23 +07:00	05/18/2021 05:03 AM
15	02-08-2021 20:47 +07:00	08/02/2021 09:07 PM
16	14-08-2021 07:00 +07:00	08/14/2021 08:00 AM
17	19-08-2021 19:20 +07:00	08/19/2021 08:00 PM
18	19-08-2021 19:33 +07:00	08/19/2021 08:03 PM
19	19-08-2021 19:52 +07:00	08/19/2021 08:02 PM
20	08-09-2021 12:40 +07:00	09/08/2021 01:00 PM
21	08-09-2021 13:05 +07:00	09/08/2021 02:05 PM
22	08-09-2021 20:49 +07:00	09/08/2021 09:09 PM
23	09-09-2021 22:39 +07:00	09/09/2021 11:09 PM
24	10-09-2021 07:00 +07:00	09/10/2021 08:00 AM
25	10-09-2021 12:35 +07:00	09/10/2021 01:05 PM
26	10-09-2021 23:00 +07:00	09/10/2021 12:00 AM
27	13-09-2021 10:55 +07:00	09/13/2021 11:05 AM
28	14-09-2021 07:00 +07:00	09/14/2021 08:00 AM
Sheet1

Cell Formulas
Range	Formula
B2:B28	B2	=DATE(MID(A2,7,4),MID(A2,4,2),LEFT(A2,2))+TIME(MID(A2,12,2)+8-MID(A2,19,2),MID(A2,16,2),)

 

[Phuoc]

Book1
	A	B
1	Created At	Date Format
2	02-03-2020 03:07 +07:00	03/02/2020 04:07
3	02-03-2020 03:10 +07:00	03/02/2020 04:10
4	02-03-2020 03:18 +07:00	03/02/2020 04:18
5	02-03-2020 03:22 +07:00	03/02/2020 04:22
6	08-08-2020 23:59 +07:00	08/09/2020 00:59
7	21-09-2020 08:56 +07:00	09/21/2020 09:56
8	23-09-2020 13:27 +07:00	09/23/2020 14:27
9	02-01-2021 23:22 +07:00	01/03/2021 00:22
10	22-02-2021 12:40 +07:00	02/22/2021 13:40
11	01-03-2021 19:09 +07:00	03/01/2021 20:09
12	09-04-2021 00:21 +07:00	04/09/2021 01:21
13	13-05-2021 22:59 +07:00	05/13/2021 23:59
Sheet1

Cell Formulas
Range	Formula
B2:B13	B2	=DATE(MID(A2,7,4),MID(A2,4,2),LEFT(A2,2))+MID(A2,12,5)-RIGHT(A2,5)+"8:00"