Calc time elapsed
I keep a database of when a certain event occurred, each record with a date field and a time field (in 24 hour format).
I'd like to be able to calculate the elapsed time, in hours and minutes (in decimal form), between the date and time on one record to the date and time of the previous record. It works as intended when the time in the previous record is on the same date, but I get negative results when the time of the previous record is on a different date (see attached screenshot).
I have a Duration "dur" calculation field, result as number, with formula:
(Hour ( time )+Minute(time)/60)-(Hour ( prevtime )+Minute(prevtime)/60)
I also have a Previous Time "prevtime" calculation field, result as time, with formula:
If((Get(RecordNumber)-1) ≠ 0;GetNthRecord(time;Get(RecordNumber)-1);0)