Calculate Duration from date time fields

[yaman123]

Hi, 

Is there a way to calculate the duration in hours and minutes from a date time field? 

e.g From Date is 24/08/2021 04:03 and To Date is 25/08/2021 06:08. The Duration should be 26:05. 

Thanks

[v-zhangti · Accepted solution]

Hi, @yaman123 

You can try the following methods.

1. First convert all intervals to seconds.

Seconds =
DATEDIFF ( [Start time], [End time], SECOND )

2. Then convert the seconds to hour-minute-second format.

Duration =
INT ( [Seconds] / 3600 ) & ":"
    & INT ( ( [Seconds] - INT ( [Seconds] / 3600 ) * 3600 ) / 60 ) & ":"
    & [Seconds]
        - INT ( ( [Seconds] - INT ( [Seconds] / 3600 ) * 3600 ) / 60 ) * 60
        - INT ( [Seconds] / 3600 ) * 3600

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jtetz30]

Also, this will yield a string so I cannot math it further... Is this really the only answer

[jtetz30]

In excel, all I need to do is subract the end date/time from the start date/time and format as [h]:mm to get hours and minutes.  This code is how I have to do that calculation is LESS sophisticated applications, which is annoying. WHY would the mature calculation be removed from the newer tool set?

[yaman123]

Thanks, that does give me the reuslts I want but can it be formatted to Time so I can take the Average Time? 

[v-zhangti]

Hi, @yaman123 

Power BI does not allow converting more than 24 hours to time. Maybe you can get the average from seconds and convert to time format.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[negi007]

@yaman123 

You can do it in both powerquery and dax

for simplicity I followed below steps in power query

1. added a column to calc hours

(Duration.Days([end date]-[start date])*24) + (Duration.Hours([end date]-[start date]))

2. added another col to calc minutes

Duration.Minutes([end date]-[start date])

3. added another col to calc seconds

Duration.Seconds([end date]-[start date])

4. then merge all columns like below

Text.Combine({Text.From([Hours], "en-US"), ":", Text.From([Seconds], "en-US"), Text.From([Minutes], "en-US"), ":", Text.From([Seconds], "en-US"), Text.From([Seconds], "en-US")})

below is the output

i am also attaching pbix file for your reference.

you can refer to below link as well for help

https://community.powerbi.com/t5/Desktop/Calculating-day-time-difference/m-p/321189