Calculate intervals of a DateTime field? 1

[cordelia]

Hi! I'm trying to calculate how much time has passed on a DateTime field in CR 10, but am struggling with the logic.

Here's a sample from my report:
STATUS: TRANSACTION DATE: LOGIN:
------- ----------------- ------
REQ MATL 1/11/2010 7:25:49AM JDOE
BUYER 2 1/11/2010 11:27:37AM BMSITH
MATLS ORDERED 1/11/2010 1:46:03PM BSMITH
CLOSED/RECVD 1/15/2010 3:16:37PM BSMITH

Essentially I'm trying to determine the length of time between each change in status. How long did it take for the status to go from 'REQ MATL' to 'BUYER 2' and so on.

I know I could use the DATEDIFF function if I was calculating the difference between two separate datetime fields, but am not sure how to calculate the interval using just the one datetime field.

Btw, the data shown above is listed in the Details section of the report.

Can anyone please help?

 

[NickieP]

create a formula field of 'time passed':

previous({table.transactiondate})-({table.transactiondate)


it will tell you the time taken from JDOE to BSMITH's transaction if placed on line 2 of your data above, if you don't want that, group on logins first then sort the data into transaction date order
(you can hide group header & footer to just show each row as now)

 

[lbass]

if onfirstrecord or
{table.groupfield} <> previous({table.groupfield}) then
0 else
datediff("d",previous({table.date}),{table.date})

The sort direction matters though. This assumes you are sorting in ascending date order.

-LB

 

[cordelia]

Thank you both so much for your suggestions!!

I used the logic provided by lbass and it worked perfectly!