Convert Date & Time to correct format

thelastpsycho

New Member
Joined
Sep 13, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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
AB
1Created AtDate Format
202-03-2020 03:07 +07:00??
302-03-2020 03:10 +07:00??
402-03-2020 03:18 +07:00??
502-03-2020 03:22 +07:00??
608-08-2020 23:59 +07:00??
721-09-2020 08:56 +07:00??
823-09-2020 13:27 +07:00??
902-01-2021 23:22 +07:00??
1022-02-2021 12:40 +07:00??
1101-03-2021 19:09 +07:00??
1209-04-2021 00:21 +07:00??
1313-05-2021 22:59 +07:00??
1418-05-2021 04:23 +07:00??
1502-08-2021 20:47 +07:00??
1614-08-2021 07:00 +07:00??
1719-08-2021 19:20 +07:00??
1819-08-2021 19:33 +07:00??
1919-08-2021 19:52 +07:00??
2008-09-2021 12:40 +07:00??
2108-09-2021 13:05 +07:00??
2208-09-2021 20:49 +07:00??
2309-09-2021 22:39 +07:00??
2410-09-2021 07:00 +07:00??
2510-09-2021 12:35 +07:00??
2610-09-2021 23:00 +07:00??
2713-09-2021 10:55 +07:00??
2814-09-2021 07:00 +07:00??
Sheet1
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,899
Office Version
  1. 2016
Platform
  1. Windows
Book1.xlsx
AB
1Created AtDate Format
202-03-2020 03:07 +07:0003/02/2020 04:07 AM
302-03-2020 03:10 +07:0003/02/2020 04:00 AM
402-03-2020 03:18 +07:0003/02/2020 04:08 AM
502-03-2020 03:22 +07:0003/02/2020 04:02 AM
608-08-2020 23:59 +07:0008/08/2020 12:09 AM
721-09-2020 08:56 +07:0009/21/2020 09:06 AM
823-09-2020 13:27 +07:0009/23/2020 02:07 PM
902-01-2021 23:22 +07:0001/02/2021 12:02 AM
1022-02-2021 12:40 +07:0002/22/2021 01:00 PM
1101-03-2021 19:09 +07:0003/01/2021 08:09 PM
1209-04-2021 00:21 +07:0004/09/2021 01:01 AM
1313-05-2021 22:59 +07:0005/13/2021 11:09 PM
1418-05-2021 04:23 +07:0005/18/2021 05:03 AM
1502-08-2021 20:47 +07:0008/02/2021 09:07 PM
1614-08-2021 07:00 +07:0008/14/2021 08:00 AM
1719-08-2021 19:20 +07:0008/19/2021 08:00 PM
1819-08-2021 19:33 +07:0008/19/2021 08:03 PM
1919-08-2021 19:52 +07:0008/19/2021 08:02 PM
2008-09-2021 12:40 +07:0009/08/2021 01:00 PM
2108-09-2021 13:05 +07:0009/08/2021 02:05 PM
2208-09-2021 20:49 +07:0009/08/2021 09:09 PM
2309-09-2021 22:39 +07:0009/09/2021 11:09 PM
2410-09-2021 07:00 +07:0009/10/2021 08:00 AM
2510-09-2021 12:35 +07:0009/10/2021 01:05 PM
2610-09-2021 23:00 +07:0009/10/2021 12:00 AM
2713-09-2021 10:55 +07:0009/13/2021 11:05 AM
2814-09-2021 07:00 +07:0009/14/2021 08:00 AM
Sheet1
Cell Formulas
RangeFormula
B2:B28B2=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),)
 
Solution

Phuoc

Active Member
Joined
Apr 29, 2016
Messages
458
Office Version
  1. 2016
Book1
AB
1Created AtDate Format
202-03-2020 03:07 +07:0003/02/2020 04:07
302-03-2020 03:10 +07:0003/02/2020 04:10
402-03-2020 03:18 +07:0003/02/2020 04:18
502-03-2020 03:22 +07:0003/02/2020 04:22
608-08-2020 23:59 +07:0008/09/2020 00:59
721-09-2020 08:56 +07:0009/21/2020 09:56
823-09-2020 13:27 +07:0009/23/2020 14:27
902-01-2021 23:22 +07:0001/03/2021 00:22
1022-02-2021 12:40 +07:0002/22/2021 13:40
1101-03-2021 19:09 +07:0003/01/2021 20:09
1209-04-2021 00:21 +07:0004/09/2021 01:21
1313-05-2021 22:59 +07:0005/13/2021 23:59
Sheet1
Cell Formulas
RangeFormula
B2:B13B2=DATE(MID(A2,7,4),MID(A2,4,2),LEFT(A2,2))+MID(A2,12,5)-RIGHT(A2,5)+"8:00"
 

Forum statistics

Threads
1,148,423
Messages
5,746,596
Members
424,032
Latest member
pochie2741

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top