Convert minutes into Hours; Minutes; and Seconds (not days) getting a calculated column able to sum

[christianfcbmx]

Hi guys

I have an excel file with 2 dates and over PB I could get the result expected (the diff in minutes and then convert into hours, minutes, and seconds)...but that new calculated column can not be switched from text format (the way it is now) to date or time or number or anything to sum, get averages etc. PB doesnt allows it I guess... anyone could help me out!!!!

[christianfcbmx]

And this is the formula I used in power BI to get the calculated column:

Duración (7*24) = var hourNo=INT([TotalminutesDiff(7*24)]/60)
var minuteNO=MOD('EXTR-INC&WO-Genérico v4 0 (Chil'[TotalminutesDiff(7*24)],60)
var secondNo=INT(([TotalminutesDiff(7*24)]-INT([TotalminutesDiff(7*24)]))*60)
return
FORMAT(hourNo,"#000")&":"&FORMAT(minuteNO,"#00")&":"&FORMAT(secondNo,"#00")

[v-jiascu-msft]

Hi @christianfcbmx,

Did you try the formula like this?

Duración (7*24) =
VAR hourNo =
    INT ( [TotalminutesDiff(7*24)] / 60 )
VAR minuteNO =
    MOD ( 'EXTR-INC&WO-Genérico v4 0 (Chil'[TotalminutesDiff(7*24)], 60 )
VAR secondNo =
    INT ( ( [TotalminutesDiff(7*24)] - INT ( [TotalminutesDiff(7*24)] ) ) * 60 )
RETURN
    TIME ( hourNo, minuteNO, secondNo )

Best Regards,

Dale

[christianfcbmx]

...still an error :(,,,,thank you Thanks Dale anyways!

[v-jiascu-msft]

Hi @christianfcbmx,

The format 00003:07:48 isn't a valid numeric format in Power BI. Maybe you can use the Text mode to display and use another approach to calculate the numeric calculation.

About the format.

format =
VAR allseconds =
    DATEDIFF ( [Start], [End], SECOND )
VAR days =
    INT ( allseconds / 24 / 60 / 60 )
VAR hours =
    MOD ( INT ( allseconds / 60 / 60 ), 24 )
VAR minutes =
    MOD ( INT ( allseconds / 60 ), 60 )
VAR seconds =
    MOD ( allseconds, 60 )
RETURN
    days & "d: "
        & hours
        & "h: "
        & minutes
        & "m: "
        & seconds
        & "s"

About the total calculation. You can sum the numeric values fisrt, then format to d:h:m:s.

Best Regards,

Dale

[christianfcbmx]

This is bacicly the result I´m looking for: