Separate Date and Time

[asad]

Hello All,

I have a huge amount of data that I need to separate dates and times from. It looks like this:

12/03/20 12:04:20 am +11:00

12/03/20 11:58:54 am +11:00

12/03/20 12:01:04 pm +11:00

How can I separate Dates and Times in dd/mm/yyyy and [h]:mm:ss formats respectively

Thanks for your help.

Asad

 

[asad]

I have used

=LEFT(A2,SEARCH(" ",A2)-1)*1

to get the date and this is working fine.

I have used

=MID(A2,SEARCH(" ",A2)+1,11)

as a first step to get the time and this also works fine but it is still text. If I then get rid of am and pm it makes all times after 12:00 as pm although a lot of them are am. I have unable to work out how can I get this done.

 

[sandy666]

with Power Query

DT		DT	Date	Time
12/03/20 12:04:20 am +11:00		12/03/2020 00:04	12/03/2020	00:04:20
12/03/20 11:58:54 am +11:00		12/03/2020 11:58	12/03/2020	11:58:54
12/03/20 12:01:04 pm +11:00		12/03/2020 12:01	12/03/2020	12:01:04

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"DT", type datetimezone}}),
    Date = Table.AddColumn(Type, "Date", each DateTime.Date([DT]), type date),
    Time = Table.AddColumn(Date, "Time", each DateTime.Time([DT]), type time)
in
    Time

 

[asad]

Thanks a lot sandy666

 

[sandy666]

You are welcome