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

ppm1's avatar
ppm1
Solution Sage
3 years ago

You can do it with a formula like this in the popup box when you add a custom column.

 

let 
sign = if [Hours] < 0 then "-" else ""
in 
sign & Duration.ToText(Duration.From( Number.Abs([Hours]/24)))

 

However, not sure if you plan to add those up, etc., but it is better practice to keep them as a decimal (in days, divide by 24) and then FORMAT at the end.

Calculate and Format Durations in DAX – Hoosier BI

 

Pat

spittingfire's avatar
spittingfire
Regular Visitor
3 years ago

Thanks Pat,

These will not be summed up as I expect that the end result will be in text format.

 

Also once the hours goes over 24 in you solution it changes to -2 days, 15 hours and 45 minutes.  I will like it all to strictly be in hh:mm so in this case -63:45