Convert DateTime to Total HH:MM:SS

[Rince91]

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...

[harshnathani]

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)

[FrankAT · Accepted solution]

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]

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?

[Rince91]

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.

[AnkitKukreja]

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.

[amitchandak]

@Rince91 , Not very clear to me

https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...

https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-i...

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?GroupId=547&MessageK...