Forum Discussion
Duration between two times
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?
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)
1 Reply
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)