Convert Excel Serial Date to normal m/d/yyyy hh:mm

[lardo5150]

I have created a flow that grabs an excel, does some stuff, and then imports it to SharePoint.

I then grab the SharePoint list.

The problem is the date is still in the Excel format:

Examples:

43766.6083449074

43753.594837963

43747.4251041667

I tried to change the column from ABC/123 to Date, and also tried Date Time.

I get an error.

I am assuming I have to create a new column and convert this.

I am not finding a clear answer on how to do this.  Seen a few different formulas but nothing that seemed like it was for my situation.

[amitchandak]

It should add seconds or day in 1900/01/01

date = dateadd(date(1900,01,01),[Date Number],second)

Or

date = dateadd(date(1900,01,01),[Date Number],Day)

[lardo5150]

Throwing an error, does not like dateadd.

The [ClosedDateTime] is the column that those excel serials are in.

[amitchandak]

@lardo5150 

That was dax. M you have Date.AddDays.

https://docs.microsoft.com/en-us/powerquery-m/date-adddays

Date.AddDays(#date(1900, 01, 01), [Days])

[lardo5150]

I am doing something wrong, but not sure what.

[amitchandak]

This function only takes Int

Date.AddDays(#date(1900,01,01),Number.Round([Date]))

[lardo5150]

still just getting an error

[lardo5150]

bumping up as I am looking for a solution on this one, as it is the last piece of the project I have been working on.

[lardo5150]

ok, so I SPLIT the ClosedDateTime column by using the deliminator of (.)

That created ClosedDateTime.1 and ClosedDateTime.2

I used this for ClosedDateTime.1

Date.AddDays(#date(1900, 01, 01), [ClosedDateTime.1])

This gave me the date, which is great.

But I can't get the time in ClosedDateTime.2.  Do you know what the correct expression is?

[V-lianl-msft · Accepted solution]

Hi @lardo5150 ,

You can try steps like below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NcjBCcAwDAPAXfwuxqlkOZ4leP81AoXe884xoiRXbJAdRZvny4Rnc6Na+I/lfHMFl1Q2cwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type number}}),
    #"Split Column by Character Transition" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"date", type text}}, "en-US"), "date", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"date.1", "date.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Character Transition",{{"date.1", Int64.Type}, {"date.2", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Date.AddDays(#date(1900, 01, 01), [date.1])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Time.From([date.2])),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", type date}, {"Custom.1", type time}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type2", {{"Custom", type text}, {"Custom.1", type text}}, "en-US"),{"Custom", "Custom.1"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Merged Columns",{{"Merged", type datetime}})
in
    #"Changed Type3"

Here is the sample pbix.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lardo5150]

EXACTLY what I was looking for and I was able to follow what you were doing.

Providing the sample was a BIG help.

THANK YOU

[lardo5150]

oops, wrong screenshot