Forum Discussion
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 below:
I need to covert it to a total durtaion i.e. 48:53:20 (HH:MM:SS) so that i can take an average of these values in another measure.
Any ideas how to do this? i have tried datediff but that only returns it ina single format of HOURS or Minutes etc...
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
6 Replies
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
Rince91 , Not very clear to me
Sorry to try and clarify:
If a repsonse to a ticket is 1day 4 hours and 38 minutes 20 seconds the format of that data in the database i have access to is:
02/01/4000 04:38:20
I need this date time in the format of:
28:38:20
So converting the day to 24hours then adding it to the other 4hours 38 minutes 20 seconds, to give a total durtaion in a time format.
Once its in that format i can then make a measure which takes the average of those reposne times.
Hi Rince91
You are going in right direction, you can create 3 calculated columns for hours, min and sec and then concate them or you can do the same in query editor, below is example to get time in hours.
TimeIn Hours = Duration.TotalHours(Duration.From ( [END_DATE] - [START_DATE])).
3rd approach is what Amit has shared you can refer to that on Radacad.
Hi Rince91 ,
You can check this blog.
https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
