Convert Number into hours and minutes

[Anonymous]

Hello Power Bi super solvers,

Could you help me get over the hill by converting a number into hours?

Background: My data has a field - 30-minute session. The row has 0, 1, 2, 3, etc. Each number represents a 30-minute session. For example, 4 means four sessions at 30 minutes each. Therefore, it should equal two hours.

Could you nudge me in the right direction to show the output as E.G. 11:30, 24:00, etc.?

Any resource is greatly appreciated.

Thank you

Luis

Sample Tables

Student Name	Student ID	Tutor	Sessions Week	30-minute Sessions	Sessions Weeks (Groups)
Luna Jeremiah	82272	Binder, Judy	2-1 Sessions	11	First Six Weeks
Luna Jeremiah	82272	Binder, Judy	3-1 Sessions	8	Second Six Weeks
Luna Jeremiah	82272	Binder, Judy	3-3 Sessions	2	Third Six Weeks
Monarrez, Kamila	46278	Binder, Judy	1-4	12	First Six Weeks
Monarrez, Kamila	46278	Binder, Judy	2-4	12	Second Six Weeks
Monarrez, Kamila	46278	Binder, Judy	3-4	4	Third Six Weeks

Envisioned Output

Student Name	Student ID	First Six Weeks	Second Six Weeks	Third Six Weeks	Total Hours (HH: MM)
Monarrez, Kamila	46278	06:00	06:00	02:00	14:00
Luna Jeremiah	82272	05:30	04:00	01:00	10:30

Could someone point me in the correct direction?

Thank you.

[andhiii079845 · Accepted solution]

What happend if you have more than 24h (48 sessions x 30 min)?

Column =

var _min = 'Table (2)'[Column1]*30

var hourNo=INT(_min/60)

var minuteNO=MOD(_min,60)

RETURN  FORMAT(hourNo,"#00")&":"&FORMAT(minuteNO,"#00")

You can also change to data type time with the column.

[Anonymous]

@andhiii079845 

Apologies for the delayed response.

Total Tutoring Hours = SUM(Separate[Tutoring Time (30 Minutes each session)])

Tutoring in HH:MM =

VAR _Min = [Total Tutoring Hours]*30

VAR HourNo = INT(_Min/60)

VAR minuteNo = MOD(_Min, 60)

Return FORMAT(HourNo, "#00") & ":" & FORMAT(minuteNo, "#00")

Is that the information you were asking for me to provide?

[Anonymous]

@andhiii079845 - Thank you.

It worked exactly as expected.

Appreciate your willingness to share your expertise.

[andhiii079845 · Accepted solution]

What happend if you have more than 24h (48 sessions x 30 min)?

Column =

var _min = 'Table (2)'[Column1]*30

var hourNo=INT(_min/60)

var minuteNO=MOD(_min,60)

RETURN  FORMAT(hourNo,"#00")&":"&FORMAT(minuteNO,"#00")

You can also change to data type time with the column.

[Anonymous]

forgot to add that i am stuck on this  DAX

Tutoring in hours =

Var Total_Hours =

    SUMX(

        'Tutorial Log',

        HOUR('Tutorial Log'[30-minute Sessions] + MINUTE('Tutorial Log'[30-minute Sessions])/60

        )

Return

[andhiii079845]

I see now that you forget something after RETURN. There has to be s calculations or a return of a variable 

[Anonymous]

Hi @andhiii079845 - i think i see what you mean.

Tutoring in HH:MM =

VAR _Min = [Total Tutoring Hours]*30

VAR HourNo = INT(_Min/60)

VAR minuteNo = MOD(_Min, 60)

Return

HourNo, minuteNo

FORMAT(HourNo, "#00") & ":" & FORMAT(minuteNo, "#00")

Could you give me another friendly nudge in correcting the syntax? I believe I want variables HourNo and MinuteNo to be returned in the DAX.

Thank you for everything.

[andhiii079845]

Sorry for my late answer. 

Tutoring in HH:MM =

VAR _Min = [Total Tutoring Hours]*30

VAR HourNo = INT(_Min/60)

VAR minuteNo = MOD(_Min, 60)

Return

HourNo, minuteNo

FORMAT(HourNo, "#00") & ":" & FORMAT(minuteNo, "#00")

You can not return 2 values in one column. Like: 60 20. You have to transform it with 
FORMAT(HourNo, "#00") & ":" & FORMAT(minuteNo, "#00") in one value per row. Or you create two columns. One for hours, one for minutes.

If you have futher question, let us know what is your goal, your involved columns/table and your desired result.  One good example can make everything more clear. 🙂

[Anonymous]

Hi @andhiii079845,

No worries about the timing of the response. I appreciate your follow-up. 

I am still learning the syntax, but your response indicates that this syntax is incorrect - per the error message. I have played around with the syntax but can't get it correct.

This visual almost does what I want.

The tooltip is great

I want the Y axis to show the number of tutoring hours in HH:MM.

Any gentle nudge on my syntax is greatly appreciated. I still can't upload a PBIX. Wish that I could, would make my message and intent clearer.

[andhiii079845 · Accepted solution]

Try this. It is great that you want to understand the synthax! 🙂

Tutoring in HH:MM =

VAR _Min = [Total Tutoring Hours]*30

VAR HourNo = INT(_Min/60)

VAR minuteNo = MOD(_Min, 60)

Return

FORMAT(HourNo, "#00") & ":" & FORMAT(minuteNo, "#00")

[Anonymous]

That works! Thank you very much.

I know it is limiting to troubleshoot with a PBIX file. Hopefully, I can upload a PBIX file next time I post a problem.

[andhiii079845]

Can you share please all involved column for your dax expression?