Duration between two times

[Anonymous]

Hello all, I have the following data:

10/01/2019	23:51	01:20
30/02/2019	13:12	16:05
05/01/2019	22:40	03:20
08/02/2019	17:31	20:40

First column is data event, second is start time and thrid is end time. However, using a datediff function, it won't understand that the first line, for example, the start and end time are on different dates, and returns absurd numbers. Also, I don't have a date column referring to the third column, so I don't have 11/01/2019 in the example. Is there anyway to make this work?

[amitchandak · Accepted solution]

@Anonymous 

First of all, make sure the two columns are timestamp columns

Else use time function and make them time stamp

Start Time = Time(left([Strat],2),right([Strat],2)) // Do same with end

My suggestion would make them datetime with date , You can leave  the date part if needed

New columns

Start date time = [Date]+[Start Time]

end Date time = if([Start Time] <[End Time ],[Date]+[EndTime],([Date]+[EndTime])+1)

Now you can use date diff

Diff = datediff([Start date time],[end Date time].day)