cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## 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 power query with hours represented in a decimal format. In addition, these are negative hours.

For example.

-0.65

-7.5

-0.083333333

-63.75

Using an excel formula such as "=IF(F2<0, "-" & TEXT(ABS((F2)/24),"[hh]:mm"), F2)" I am able to return a correct text format representation of what I need

For example,

-0.65 = -00:39

-7.5 = -07:30

-0.083333333 = -00:05

-63.75 = -63:45

Is there an equivalent way of achieving this using Power Query in Excel?

If yes can someone assist me with steps and / or formula?

1 ACCEPTED SOLUTION
Community Support

``````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

6 REPLIES 6
Community Support

``````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

Regular Visitor

Thanks you so much v-cgao-msft,

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

Much, much appreciated!!

Solution Sage

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

Microsoft Employee
Regular Visitor

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

Super User

create a blank query and paste the following code into the advanced editor:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0jXQMzNVitUBssz1oAwDPQMLYwiACJgZ65kD5WIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Column1] < 0 then Number.Abs([Column1]/24) else [Column1]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type duration}}),
#"Inserted Merged Column" = Table.AddColumn(#"Changed Type1", "Merged", each Text.Combine({"-0.", Text.From([Custom], "en-CA")}), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Custom"})
in
#"Removed Columns"

Kindly confirm on the last value (only different one).

Appreciate a thumbs up if this is helpful.

Please accept this as the solution if the question is resolved.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash
Regular Visitor

As with Pat's suggesstion you both arrived with the same solution however close but not what I am hoping for.

First I am hoping for a solution that will only show the format of hh:mm.

In the last entry it's -2 days, 15 hours and 45 minutes and it should be -63:45

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.