Time Difference Between records. 1

[menuch]

How would I calculate a time difference between records. I need to calculate the time between time2 of one record with time 1 of the next and so on. I am trying to figure out downtime between jobs. Time 1 is the start time and time2 is the end time of a particular job.

Veh# Shift Start
005 Tour 2 4:37:00PM
Job# Time1 Time2
180239 5:56 pm 7:22 pm
180241 7:41 pm 9:11 pm
180243 9:11 pm 10:23 pm
180244 10:24 pm 11:33 pm

any help would be appreciated.

Thanks,
Menuch

 

[mbarron]

create the following formula:

{time1.field}-previous({time2.field})

Mike

 

[menuch]

Thanks it worked. But one question. I am using a formula and for the first record in each group I am calculating the time diff between shift start and time1 and for the first record it is blank but for the remaining groups the first record is populated. I am grouping by the Vehicle number.

 

[synapsevampire]

Try:

if onfirstrecord then
{time1.timefield}-{table.shiftstarttime}
else
{time1.timefield}-previous({time1.timefield})

-k

 

[menuch]

It still didnt work. Below is my formula. I am using a running Total to create a record number which resets on change of group. It works for each group except the first. Time1 is my 63 date time, Time 2 is my 98 date time. My shift start is my Date-time out of garage.:

global numbervar R:={#Record Number};
global datetimevar S:={@63 - Date Time};
global datetimevar N:=previous({@98 - Date Time});

if onfirstrecord Then datediff('n',{@DateTime - Out of Garage},{@63 - Date Time})
else
if R=1 Then datediff('n',{@DateTime - Out of Garage},{@63 - Date Time})
else

datediff('n',N,S)

 

[lbass]

What is in the formula {@DateTime-OutofGarage}? Does it also use the "previous" function? Also, "OnFirstRecord" is the same as "R = 1" for the first group, so you only need one of those. But I'm guessing the glitch is with the Out of Garage formula--are you sure that you want to measure shift start to job start here? This is a conceptual decision--whether you want to know that, or whether you purely want to know the difference between jobs. You could alternatively decide to to write the formula like this:

if R = 1 then 0 else
datediff("n",N,S);

-LB