Time - how to calculate the amount of time when passing over 24 hours

[JordanJackson]

Hi, 

I am looking for a soloution for calculating the time between two time columns when the time between the two has passed over 24 hours. 

I have the start time, a middle time and the end time for events occuring overnight, and so the end time will mostly be the early hours of the following day (right most column in attachment). 

Attached is an example of my data.

How can I calculate the amount of time between these columns?
Do I need a calculated column in order to state if it is the following day to allow for this?

Thanks in advance

[hokeson]

hi,

you have more options.

best would be to collect not only time but also date and calculate duration.

second option is to be sure that that the difference cant be bigger than 24 hours. if this is true than you can do two calculations based on following condition.

if startTime(20:00) is bigger then endtime (10:00) then 

      duration = diff(startTime, 23:59:59) + Diff(0:00,endTime)

else

      duration = Diff(startTime,endTime) 

function syntax may vary based on usage.

[JordanJackson]

Thank you for your reply,

I am thinking that I will create a calculated column for whether the date is the same or +1 day. Then i should be able to use DATEDIFF in order to calculate the difference in time between the two. 

I think I may be able to suggest changes to data collection going forward, but unless I do it manually I need a quick way to get dates for every entry.