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

[Debbiethenovice]

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?

[amitchandak]

@Debbiethenovice ,

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

[amitchandak]

@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])

[Debbiethenovice]

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

[HotChilli]

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

[Debbiethenovice]

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