Formula to calculate time based on a criteria

I am looking for a formula to calculate the the difference in time between two transaction types

{incilog.timestamp} = "D" (for Dispatch)

and

{incilog.timestamp} = "C" (for Last Clear)

How many HH:MM:SS were spend on the call between the Dispatch and Clear

First off you would need to do a datediff in between the two dates (in seconds) then convert it to a string to the format you want (there are formulas available out on the internet, I do not have one handy).

Thank you for your reply. The timestamps are from the same field so I am not sure how to use the DateDiff to get the difference of time in the same field.

There is a time stamp for the dispatch and a time stamp for the clear. If it were different fields, I can figure it out but there is the variable and the same filed.

DateDiff ("s",{incilog.timestamp}, {incilog.timestamp}) will not work unless I add the variable of the transaction type.... dispatched and cleared. I just cannot figure out how to do it.

Then I am assuming that they are different records and that you are grouping the report in some way.  If so, then you will need to store the first (or last date) in a global (I have better luck with shared) variable at the beginning of the grouping (Group Header) and then do the calculation in the Group Footer using the value from the global (or shared variable).

you can also join the table to itself on the PK and only pull the row with "D" in one version and "C" in the other and use that resulting data set to get the values you need

Hi! No, there are no groups.

There is a "TimeStamp" Field that puts the time of each transaction.

Since the data is housed in one field, I am not sure how to calculate the time between the transaction of Dispatched and Cleared.

DBlank's solution will work real good if you know how to write a command (i.e., TSQL query).  Otherwise you will need to group your data.