• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help in time conversion

Simi

New Member
Hi I need help to convert below format data to minutes. Kindly help

9h 54m
9h 57m
12h 48m
5h 22m
5h 4m
11h 40m
1d 13h 33m
1d 5h 47m
 

Peter Bartholomew

Well-Known Member
Assuming 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
 

Simi

New Member
Assuming 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
Thanks for your response it works for where format has only h (hour) & m (min); does not work where d (day) is also there
5h 4m
304
11h 40m
700
1d 13h 33m
#VALUE!
1d 5h 47m
#VALUE!
 

Peter Bartholomew

Well-Known Member
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.
 

AlanSidman

Well-Known Member
Ok Peter. More Elegant? Not sure. Longer? Yes. Results? Yes. However, add an line to column or change any value in column A and click on RefreshAll and it immediately updates. Oh, and it will accept values greater than 31 days.

Power Query. Here is the Mcode and the file for review.

Code:
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"
Review PQ
In the attached file
Click on any cell in the new table
On the Data Tab, click on Queries & Connections
In the right window, double click to open Query
Review PQ steps
M-code basics:
- "let" is the start of a query
- "in" is the closing of a query
- each transformation step sits in between those 2 lines
- each step line is ended by a comma, except the last one
- "Source" is always the first step (Source Data)
- After "in" you have the last step referenced
 

Attachments

Simi

New Member
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.
Thanks a ton, just a silly question, what are 2 dashes in the front of formulae for..Can you explain same
 

Peter Bartholomew

Well-Known Member
@AlanSidman
That would do it. You seem to be making PQ something of a speciality these days!
I ran through the solution in the editor and the only point at which we would diverge is whether it is better to multiply the hours and day columns in place rather than adding new columns.
At least I know who to come to for a second opinion next time I tangle with PQ :DD
 

Peter Bartholomew

Well-Known Member
@Simi
The unary negation coerces the text to a number and, since I do not wish to return a negative value, I apply negation a second time. It looks strange I know, but it was often used with the old SUMPRODUCT array formulas.
 

AlanSidman

Well-Known Member
@Peter Bartholomew
I am kind of fascinated with what you can do with PQ. Keep exploring new ways to do stuff. This past week I learned how to do parameter queries and Wow! is all I can say as to how easy it is and eliminates lots of VBA steps.

I will say that this particular posting is not the most elegant or efficient but it is possible but probably not one I would necessarily use. I did like the exercise, however.
 
Top