Subtracting records in crystal report

[ss85]

Hello,

I am trying to subtract records in a table which are in two different rows. It might be an easy one however i cant seem to do it the right way i guess.

Eg: I have a column called EventDateTime which has multiple values in it

EventDateTime
9/19/2012 5:11:07AM
9/19/2012 5:11:55AM
9/19/2012 5:20:13AM
9/19/2012 5:30:16AM

I want a formulae to subtract the second entry (9/19/2012 5:11:55AM) from the first one (9/19/2012 5:11:07AM) and give me the result in the difference in the same format 9/19/2012 0:00:48AM.

How can i get this done?

Just for the background, these are time fields from the Cisco ACD system and i want to calculate the agent state details, which is to calculate the time an agent was in ready state and when he changed the state from ready to work.

CAn someone please help me with this issue?

 

[fisheromacse]

I think you should be able to use two formulas and then place them both into at text box for display. I do not have crystal in front of my so apologize in advance for any errors or typos.

If your data field is a real DateTime Value, you should be able to use these:
//{@Date}
DATE({table.EventDateTime})
//{@Time}
TIME({table.EventDateTime})

If it is a string field, the below may work better:
//{@Date}
DATE(Split({table.EventDateTime}," ")[Ubound(Split({table.EventDateTime}," "))-2])

//{@Time}
TIME(Split(PREVIOUS({table.EventDateTime})," ")[Ubound(Split(PREVIOUS({table.EventDateTime})," "))-1])TIME(Split({table.EventDateTime}," ")[Ubound(Split({table.EventDateTime}," "))-1])

 

[fisheromacse]

oops, my first {@Time}
should have been:
TIME(PREVIOUS({table.EventDateTime}))-TIME({table.EventDateTime})

 

[ss85]

Thanks for your reply. However, what is the formulae to get the difference between two records? Also i am trying to use this formulae right now {AgentStateDetail.eventDateTime} - previous ({AgentStateDetail.eventDateTime}) but this is giving me the result as 0.00 where as the difference between the first and second entry is 48secs.

 

[ss85]

Thanks this helped alot. However i made some changes to it for it to work perfectly fine. Is there any other way to have a formulae to give in the final result as such instead to run to formulae and combining it in the text field?
I would ideally like to have the result in date time format?

 

[fisheromacse]

you can use another formula as the display instead of a text field.

//{@DTDisplaky}
IF onfirstrecord=TRUE then ""
ELSE {@Date) &" "& {@Time}

 

[ss85]

Thanks alot for your help.