Forum Discussion

spittingfire's avatar
spittingfire
Regular Visitor
3 years ago
Solved

Excel Power Query equivalent for an excel formula for converting negative decimal hours

Hi there, I am super new to Power Query in Excel and hoping to get some help. I am not sure if what I am asking is possible or not but thought I'd reach out and ask. I have a column in an excel pow...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi spittingfire ,

     

    Please try this:

    let
  sign = if [Hours] < 0 then "-" else "", 
  hh_1 = Number.RoundDown(Number.Abs([Hours])), 
  hh_2 = if hh_1 >= 10 then Text.From(hh_1) else "0" & Text.From(hh_1), 
  mm_1 = Number.RoundUp((Number.Abs([Hours]) - hh_1) * 60), 
  mm_2 = if mm_1 >= 10 then Text.From(mm_1) else "0" & Text.From(mm_1)
in
  sign & hh_2 & ":" & mm_2

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi spittingfire ,

 

Please try this:

let
  sign = if [Hours] < 0 then "-" else "", 
  hh_1 = Number.RoundDown(Number.Abs([Hours])), 
  hh_2 = if hh_1 >= 10 then Text.From(hh_1) else "0" & Text.From(hh_1), 
  mm_1 = Number.RoundUp((Number.Abs([Hours]) - hh_1) * 60), 
  mm_2 = if mm_1 >= 10 then Text.From(mm_1) else "0" & Text.From(mm_1)
in
  sign & hh_2 & ":" & mm_2

Best Regards,
Gao
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

  • spittingfire's avatar
    spittingfire
    Regular Visitor
    3 years ago

    Thanks you so much v-cgao-msft,

    This is exactly the result that I was looking for and expected.

     

    Much, much appreciated!!