need help with an age formula

[Fadius]

I have a table which is a query result set from an application. I need to create a formula to figure the age or amount of time it took to send an application from the time all the data was entered. Table structure example:

Name Payor Type DateSent DateReceived
John Doe NA Setup 10/15/2010
John Doe Verizon Initial 01/15/2011
John Doe MCI Renew 11/03/2010


In the example data above, I need a formula that will tell me how long it took to send the application (represented by types: Initial and Renew) from the date received (represented by the type: setup)

My final report will look like:

John Doe
----Verizon 92
----MCI 17

 

[jmd0252]

looks like you are just "counting days". If the dates are defined as a "date field", instead of say a string. Then just do the math, and subtract the fields, the result will be the days different. Crystal has enough "smarts", to be able to do that.

 

[lbass]

Insert a group on Name and then create a formula {@Setup} like this:

if {table.type} = "Setup" then
{table.date}

Then create a second formula to place in the detail section:

datediff("d",{table.date},maximum({@Setup},{table.name}))

Then suppress the detail section conditionally with:

{table.type} = "Setup"

Show only the type field and the datediff formula in the detail section.

-LB