convert Timestamp to two colomns with date & time

[Frixel]

Hi all,

I have this Timestamp (23-06-2020 09:12:22) also with ( )

Can i convert this in one step to a colomn with date and a colomn with time?

This noob have looking for a how to but couldn`t find it.

[az38 · Accepted solution]

Hi @Frixel 

try

Date Column = DATEVALUE(MID([Timestamp Column], 2, 10))

and

Time Column = TIMEVALUE(MID([Timestamp Column], 13, 8))

then format its columns as a date and time respectively in Column tools ribbon

[v-eqin-msft · Accepted solution]

Hi @Frixel ,

According to my understanding, you want to split the Timestamp column into Date column and Time column without “(”,”)”,right?
For my example, I did it in two ways.

1.Using the following formula:

Date column =
LEFT ( SUBSTITUTE ( [Timestamp], "(", "" ), 10 )

Time column =
RIGHT ( SUBSTITUTE ( [Timestamp], ")", "" ), 7 )

And then change the two columns’ Data type from “Text” into “Date” and “Time” separately.

My example visualization looks like this:

2.Apply some steps in Query Editor(Advanced Editor)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0jDTNTLVNTIwMlAwsLQyNLIyMtJUitUBSljoGhljlTA00DUwh8sYG1gZGABlYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TimeStamp = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"(","",Replacer.ReplaceText,{"TimeStamp"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",")","",Replacer.ReplaceText,{"TimeStamp"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value1",{{"TimeStamp", type datetime}}),
    #"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([TimeStamp]), type date),
    #"Inserted Time" = Table.AddColumn(#"Inserted Date", "Time", each DateTime.Time([TimeStamp]), type time)
in
#"Inserted Time"

Are these results what you want? If you have any questions, please upload some data samples and expected output.
Please do mask sensitive data before uploading.

Best Regards,
Eyelyn Qin

[az38 · Accepted solution]

Hi @Frixel 

try

Date Column = DATEVALUE(MID([Timestamp Column], 2, 10))

and

Time Column = TIMEVALUE(MID([Timestamp Column], 13, 8))

then format its columns as a date and time respectively in Column tools ribbon