Solved
Crystal Report Average "Time in Program"
Posted on 2009-07-06
Hello,
I have a report that includes a "Time in Program" total using the formula below:
Local DateTimeVar dstart := {collection_date.start_date};
Local DateTimeVar dend:= {@isnull};
Local StringVar yy := '';
Local StringVar mm := '';
Local StringVar dd := '';
// years
if DateAdd ('yyyy', -(DateDiff ('yyyy',dstart ,dend )), dend) >= dstart
then yy := totext(DateDiff ('yyyy',dstart ,dend ),0)
else yy := totext(DateDiff ('yyyy',dstart ,dend )-1,0)
;
// months
if month(dend) = month(dstart) // same month
then (
if day(dend) >= day(dstart)
then mm := '0'
else mm := '11';
)
else // diff month
(if DateAdd ('m', -(DateDiff ('m',dstart ,dend )), dend) >= dstart
then mm := totext(remainder(DateDiff ('m',dstart ,dend ),12),0)
else mm := totext(remainder(DateDiff ('m',dstart ,dend ),12)-1,0);)
;
// days
if day(dend) < day(dstart) //earlier
then dd := totext(datediff('d'
,dateserial(year(DateAdd ('m', -1, dend))
,month(DateAdd ('m', -1, dend))
,day(dstart))
,dend),0);
if day(dend) = day(dstart) //same
then dd := '0';
if day(dend) > day(dstart) //later
then dd := totext(day(dend) - day(dstart),0);
// print out with spacing - can be right or left justified
iif (len(yy) = 1, ' ', '')
& yy & 'y'
& iif (len(mm) = 1, ' ', ' ')
& mm & 'm'
& iif (len(dd) = 1, ' ', ' ')
& dd & 'd'
I was told to use this formula to calculate the years, months and days that a person was in a group. I need to create a formula to average the "Time in Program" for each program. I currently have the report grouped by:
G1-State, G2-(parent) Program, G3-Program then G4-consumer.
I would like to have an average in G3, G2 and G1
Thanks