Thanks for your response it works for where format has only h (hour) & m (min); does not work where d (day) is also thereAssuming these are text strings, the first 6 could be changed to times using
= TIMEVALUE( SUBSTITUTE(SUBSTITUTE(E4, "h ",":"),"m","") )
Number formatting using
[h]\h mm\m
would mimic the original format, whereas
[m] "min"
would show the result as minutes duration, e.g.
594 min
5h 4m | 304 |
11h 40m | 700 |
1d 13h 33m | #VALUE! |
1d 5h 47m | #VALUE! |
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Days", each if Text.Contains([Column1.1],"d") then[Column1.1] else "0d"),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Hours", each if Text.Contains([Column1.1],"h") then [Column1.1] else [Column1.2]),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Minutes", each if Text.Contains([Column1.2], "m") then [Column1.2] else[Column1.3]),
#"Split Column by Character Transition" = Table.SplitColumn(#"Added Custom2", "Days", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Days.1", "Days.2"}),
#"Split Column by Character Transition1" = Table.SplitColumn(#"Split Column by Character Transition", "Hours", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Hours.1", "Hours.2"}),
#"Split Column by Character Transition2" = Table.SplitColumn(#"Split Column by Character Transition1", "Minutes", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Minutes.1", "Minutes.2"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition2",{"Column1.1", "Column1.2", "Column1.3", "Days.2", "Hours.2", "Minutes.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Days.1", Int64.Type}, {"Hours.1", Int64.Type}, {"Minutes.1", Int64.Type}}),
#"Inserted Multiplication" = Table.AddColumn(#"Changed Type2", "Multiplication", each [Days.1] * 1440, type number),
#"Inserted Multiplication1" = Table.AddColumn(#"Inserted Multiplication", "Multiplication.1", each [Hours.1] * 60, type number),
#"Inserted Sum" = Table.AddColumn(#"Inserted Multiplication1", "Addition", each List.Sum({[Minutes.1], [Multiplication], [Multiplication.1]}), type number),
#"Removed Columns1" = Table.RemoveColumns(#"Inserted Sum",{"Days.1", "Hours.1", "Minutes.1", "Multiplication", "Multiplication.1"})
in
#"Removed Columns1"
I was rather hoping someone might come up with a more elegant approach!
= --SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(string, "h ",":"),"m",""),"d","/1/1900")
seems to work, provided you do not exceed 31 days.