How to convert seconds into hh:mm format in Power Bi?

Question

In Power Bi, I have a table that contains Name and TimeSpent by user in seconds. I want to convert total seconds spent by all users into duration format (hh:mm)

When I am getting seconds in hh:mm format for each user from database query, the values are coming up like these 12:63 etc. After importing these values into power bi, I tried to set its datatype to DateTime format but power bi shows an error saying that it is not a valid value. If I set the datatype of the column as string then strings dont add up.

What can be the ideal way to do it?

https://stackoverflow.com/questions/1262497/how-to-convert-seconds-to-hhmmss-using-t-sql — user5226582, Aug 09 '17 at 07:44

score 7 · Answer 1 · edited Apr 18 '23 at 22:02

7

you can solve this in one line:

measure = FORMAT(TIME(0, 0, tableNAME[your_column]), "HH:mm:ss")

edited Apr 18 '23 at 22:02

aduguid

3,099
6
18
37

answered Jun 14 '19 at 18:55

Jonas Correa

71
1
2

For this to work on a new calculated column, you need to replace ; with , – Shiloh Dec 09 '19 at 01:03

Foxan Ng · Accepted Answer · 2017-08-10T07:51:25.260

4

You can try the following DAX:

HHMMSS = 
INT(Table[TimeSpent] / 3600) & ":" &
RIGHT("0" & INT((Table[TimeSpent] - INT(Table[TimeSpent] / 3600) * 3600) / 60), 2) & ":" &
RIGHT("0" & MOD(Table[TimeSpent], 3600), 2)

Source

edited Aug 10 '17 at 07:51

answered Aug 09 '17 at 16:04

Foxan Ng

6,883
4
34
41

1

suppose my total seconds are 1625038 which are `151 hours` and `23 minutes`. Using this formula, the hours are coming `51:23`, `1` is getting truncated. – Sonali Aug 10 '17 at 05:46
@Sonali I have updated the DAX. Please check if it works. – Foxan Ng Aug 10 '17 at 07:51
Hi @Sonali Does this help solving the issue after all? If so, you can [accept this answer](https://meta.stackexchange.com/a/5235/313506) to positively close the question. – Foxan Ng Aug 25 '17 at 15:47
1

I changed it a little `IF(INT(Query1[Total Time] / 3600) = 0,"00",INT(Query1[Total Time] / 3600)) & ":" & RIGHT("0" & INT((Query1[Total Time] - INT(Query1[Total Time] / 3600) * 3600) / 60), 2)` – Sonali Aug 29 '17 at 10:27
can anyone help in adding datepart as well in above query! – Ankit Nov 27 '17 at 11:48

score 1 · Answer 3 · answered Mar 15 '18 at 06:04

Had a similar question but for D:HH:MM:SS, code below if it's of use.

DurTime (meas) = 
VAR vDur = <<<duration in CALCULATE(SUM(seconds)) >>>
  RETURN INT(vDur/86400) & ":" &                     //Days
    RIGHT("0" & INT(MOD(vDur/3600,24)),2) & ":" &    //Hours 
    RIGHT("0" & INT(MOD(vDur/60,60)),2) & ":" &      //Minutes
    RIGHT("0" & INT(MOD(vDur,60)),2)                 //Seconds

score 1 · Answer 4 · answered Apr 23 '19 at 12:37

1

DAX code:

 = TIME(0,0,SUM('Table'[Timespent]))

Then click the modelling tab and choose Format - Date Time and choose the appropriate format.

answered Apr 23 '19 at 12:37

RET

861
6
15

score 0 · Answer 5 · answered Mar 21 '18 at 15:19

0

That's a better formula, which I'm using in PBI: HHMMSS = FORMAT(TIME(int(Table[TimeSpent] / 3600); int(mod(Table[TimeSpent]; 3600) / 60);int(mod(mod(Table[TimeSpent]; 3600); 60))); "HH:mm:ss")

answered Mar 21 '18 at 15:19

Filip Yaghob

88
3

user16543787 · Answer 6 · 2022-01-11T13:08:38.270

I wanted a Power BI Measure wich is easy to read for this problem, code below if it's of use.

HH:MM = 
VAR TotalDuration = SUM(tableNAME[your_column] ) //if you use a measure just leave the SUM part out
VAR TotalHours = TRUNC (TotalDuration/3600)
VAR Min_ =  FORMAT(TRUNC(TotalDuration - TotalHours * 3600),"00")
RETURN
    TotalHours & ":" & Min_

The solution is adopted from the top answer of this question PowerBi Duration calculation in hh:mm:ss

score 0 · Answer 7 · edited Mar 18 '23 at 11:10

0

I have solved a template for this:

This will enable to convert seconds into D:HH:MM:SS format

D:HH:MM:SS = 

VAR D = int([AVG CBT]/86400)

VAR A_D = [AVG CBT]- D * 86400

VAR HH = RIGHT(0 & INT(A_D/3600),2)

VAR A_HH = A_D - HH * 3600

VAR  MM = RIGHT(0 & INT(A_HH/60),2)

VAR A_MM = A_HH - MM*60

VAR SS = RIGHT(0 & INT(A_MM),2)

RETURN
D & ":" & HH & ":" & MM & ":" & SS

I think it will resolve most needs.

Kishan.

edited Mar 18 '23 at 11:10

Yash Mehta

2,025
3
9
20

answered Mar 11 '23 at 11:31

Kishan Thacker

9
2

While pointless to begin with (see Jonas Correa's answer), I find timo's 2018 rendition more readable. – greybeard Mar 20 '23 at 14:05

score 0 · Answer 8 · edited Mar 30 '23 at 16:15

0

ROUNDDOWN(TableName[ColumnName]/3600,0)&":"&ROUNDDOWN(TableName[ColumnName]/60,0)-ROUNDDOWN(TableName[ColumnName]/3600,0)*60&":"&TableName[ColumnName]-ROUNDDOWN(TableName[ColumnName]/3600,0)*3600-(ROUNDDOWN(TableName[ColumnName]/60,0)-ROUNDDOWN(TableName[ColumnName]/3600,0)*60)*60

edited Mar 30 '23 at 16:15

Tyler2P

2,324
26
22
31

answered Mar 17 '23 at 12:25

Aleksandar Vučićević

1

This will give appropriate result, test it. – Aleksandar Vučićević Mar 17 '23 at 12:25

How to convert seconds into hh:mm format in Power Bi?

8 Answers8