CODEWITHSUNDEEP
excelpowerbipowerquerydatetime-format
Ajmal Rasheed
Ajmal Rasheed

Reputation: 21

How to convert HHMM into time in minutes in excel

I have a column in excel in hhmm format. Note that there is no ':' in the middle. Also they are not zero padded. For example 620 is 6:20 AM. 1820 is 18:20 or 6:20 PM. How do I convert this column into minutes. My intention is to subtract two such columns to obtain the time difference in minutes.

Upvotes: 0

Views: 451

Answers (2)

mkRabbani
mkRabbani

Reputation: 16908

You can perform some transformation in Power Query Editor for your purpose as stated below-

Let your data looks like below-

enter image description here

Here is Advanced Editor code for new steps-

let
    //Your existing code,

    #"Split Column by Position" = Table.SplitColumn(#"previous_step_name", "time_in_number", Splitter.SplitTextByPositions({0, 2}, true), {"time_in_number.1", "time_in_number.2"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Position",{"time_in_number.1", "time_in_number.2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns",{{"Merged", type time}})
in
    #"Changed Type2"

Here is the final output-

enter image description here

Upvotes: 0

basic
basic

Reputation: 11968

Formula:

=INT(A1/100)*60+MOD(A1,100)

converts your timevalue into minutes

enter image description here

Upvotes: 1

Related Questions