topic Re: Find the duration in Hours & Mins when two columns show time only and goes past midnight in DAX Commands and Tips

Find the duration in Hours & Mins when two columns show time only and goes past midnight

Debbiethenovice — Tue, 06 Oct 2020 14:58:08 GMT

Hi All, I have the below columns and I need to know how many hours/duration a shift is. I applied =Duration.From([EndTime]-[StartTime]) in power query, custom column but it doesnt work. I also tried others and then I get error. Can someone please help?

Re: Find the duration in Hours & Mins when two columns show time only and goes past midnight

amitchandak — Tue, 06 Oct 2020 16:22:20 GMT

Debbiethenovice ,

If([start time]>=[end time] ,[start time]- [end time] , time(24,0,0) -([start time]- [end time]))

Re: Find the duration in Hours & Mins when two columns show time only and goes past midnight

Debbiethenovice — Tue, 06 Oct 2020 16:46:57 GMT

Hi Armitchandak, Thank you, would I use that in Power query, custom column please? I did try and I got the below error.

Re: Find the duration in Hours & Mins when two columns show time only and goes past midnight

amitchandak — Tue, 06 Oct 2020 16:51:28 GMT

Debbiethenovice , Try in power query in new column

If [start time]>=[end time] then [start time]- [end time] else #duration(0,24,0,0) -([start time]- [end time])

Re: Find the duration in Hours & Mins when two columns show time only and goes past midnight

Debbiethenovice — Wed, 07 Oct 2020 07:53:49 GMT

Sorry, i tried that, didn't get an error on the syntex but the result shows errors: what am i doing wrong?

Re: Find the duration in Hours & Mins when two columns show time only and goes past midnight

HotChilli — Wed, 07 Oct 2020 10:10:00 GMT

Hi Debbiethenovice welcome to the forum.

First, don't trust some formula that someone probably wrote in about 15 seconds and fired off to you.

Second, you might as well learn to debug these Power Query statements. Here's how:

Click in the same cell as an error and you'll get the error message below. These don't often help that much but it'll be a start.

Also, to debug if..then statements, I often replace one of the clauses with 0 just to remove it from the situation, so try that with either the 'then' or 'else' results until you get 1/2 of your data to work.

Get back to me if you make progress