Forum Discussion

Rince91's avatar
Rince91
Helper I
6 years ago
Solved

Convert DateTime to Total HH:MM:SS

I have a column that is the raw out put of how long it has taken to respond to a ticket.   It is in the format dd/mm/yyyy hh:mm:ss   However the date starts at 01/01/4000 00:00:00. Screenshot bel...
Need Help
  • FrankAT's avatar
    FrankAT
    6 years ago

    Hi Rince91 

    power bi desktop isn't able to format time beyond 23:59:59. In Excel you can use square brackets like this [hh]:mm:ss. Use a calculated column with the following formula:

    Total Time = 
VAR _Difference = 'Table'[End] - 'Table'[Start]
VAR _Days = INT(_Difference)
VAR _Hours = HOUR(_Difference)
VAR _Minutes = MINUTE(_Difference)
VAR _Seconds = SECOND(_Difference)
VAR _DaysToHours = _Days * 24
VAR _TotalHours = _DaysToHours + _Hours
RETURN
    FORMAT(_TotalHours, "00") & ":" & FORMAT(_Minutes, "00") & ":" & FORMAT(_Seconds,"00")

     

     

    Regards FrankAT

     

FrankAT's avatar
FrankAT
Community Champion
6 years ago

Hi Rince91 

power bi desktop isn't able to format time beyond 23:59:59. In Excel you can use square brackets like this [hh]:mm:ss. Use a calculated column with the following formula:

Total Time = 
VAR _Difference = 'Table'[End] - 'Table'[Start]
VAR _Days = INT(_Difference)
VAR _Hours = HOUR(_Difference)
VAR _Minutes = MINUTE(_Difference)
VAR _Seconds = SECOND(_Difference)
VAR _DaysToHours = _Days * 24
VAR _TotalHours = _DaysToHours + _Hours
RETURN
    FORMAT(_TotalHours, "00") & ":" & FORMAT(_Minutes, "00") & ":" & FORMAT(_Seconds,"00")

 

 

Regards FrankAT

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

hi,

i tried to do that also. after i calaulated this, i try to calculate average but it dose not work becuse its string. do you have maybe solution for this?