We couldn't parse the input provided as a Time value.

[FranckGbadamass]

Hello experts,

I have excel file coming from SAP engine not correctly formatted (time displays as a text) see below

(unfortunately, I'm obliged to used it as) 😪

Picture 1

So far, when I try to convert above data into time I have this error message: We couldn't parse the input provided as a Time value.

I know it's because the hour value is greater than 23 but I don't know how to deal with.

Thank you in advance!

Best

[PhilipTreacy · Accepted solution]

Hi @FranckGbadamass 

Download example XLSX file

As you point out, these aren't actually times, so how do you want to convert them?  What do they represent?  A duration?

If so then you can convert these to durations but it takes several steps to format and convert each part of the data (days, hours, minutes, seconds).

let
    Source = "27:41:51",
    #"Imported Text" = Lines.FromText(Source),
    #"Converted to Table" = Table.FromList(#"Imported Text", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Converted to Table", "Column1", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Column1.1", "Hours_"}, {"Column1.2", "Mins"}, {"Column1.3", "Secs"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Days_", each Text.From(Number.From([Hours_]) /24)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Days", each Text.Split([Days_] ,"."){0}),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Hours", each Text.From(Number.From("." & Text.Split([Days_] ,"."){1}) * 24)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Hours_", "Days_"}),
    #"Added Custom3" = Table.AddColumn(#"Removed Columns", "Duration", each Duration.FromText([Days] & "." & [Hours] & ":" & [Mins] & ":" & [Secs])),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom3",{{"Duration", type duration}})
in
    #"Changed Type"

That code will convert this

to this

My sample code is only working on 1 column, your image shows several columns.  You can combine everything into 1 column to work on it.  If you need help doing that please supply your data file so I can work on it.

Regards

Phil