Convert Minutes to Hrs and minutes and make it sum

[Anonymous]

Hi All,

I am tryiing tp convert my table which is stored in minutes

sample

 Number            Mins

     3                    60

     4                    80 

Now I want to convert them into 1hr 0mins and 1 hr 20 mins and also want it to be able to summarize them as SUM

so in table, I can get 2 hrs 20 mins

I have tried 2 following methods, which converts the whole thing into Text and is no longer summable
Method 1

let

DecHrs = [TOTAL EXPECTED MINS]/60,

WholeHrs = Number.Round(DecHrs),

Minutes = ((DecHrs)-(WholeHrs))*60,

WholeMin = (DecHrs) - (Minutes),

FormattedTime =  Number.ToText(WholeHrs) & " hrs " & Number.ToText(Minutes) & " mins"

in

    FormattedTime)

Method 2 

let

        TotalMinutes = [TOTAL EXPECTED MINS],

        Hours = TotalMinutes / 60, WholeHrs = Number.Round(Hours),

        Minutes = (Hours- (WholeHrs))*60,

        FormattedTime = Text.PadStart(Text.From(WholeHrs), 2, "0") & ":" & Text.PadStart(Text.From(Minutes), 2, "0")

    in

        FormattedTime

Apart from that, I have thought of making 2 separate columns and using them, But feels lengthy for multiple uses

Appreciate the assistance in getting the solution and thank in advance

[Ahmedx · Accepted solution]

pls try this

[Anonymous · Accepted solution]

After summing, if anyone had negative values like I did, try this method that worked for me. Other methods made negative 1.5 to 2 on rounding.

TASK AVAILABLE HRS LEFT = 
VAR _all = SUM([MIN Table])/ 60
VAR _sign = IF(_all < 0, -1, 1)
VAR a_all = ABS(_all)
VAR _Hrs = INT(a_all)
VAR _Min = ROUND((a_all - _Hrs) * 60, 0)
RETURN 
IF(_sign < 0, "-", "") & _Hrs & ":" & FORMAT(_Min, "00")

[Ahmedx]

pls try this

[Anonymous]

Would this have the same issue as @ryan_mayu  or suppose if my minutes is 2748 it would result in 45(hh):48(nn)?

[Ahmedx · Accepted solution]

pls try this

[ryan_mayu]

@Anonymous 

you can try this

Column = time(int('Table'[Min]/60),mod('Table'[Min],60),0)

however, the problem is if the hour is more than 23, it will display incorrect.

maybe you can try to add all mins, and transfer to the text type at last.