Date Time Average

[Ohioste]

Alright, so I've gone through the majority of the threads and I think I'm almost there.

I'm trying to determine the amount of time it takes from when a task is created to when it is completed.

I have two datetime formulas. One for start and one for end.

I'm all the way to trying to get an average of this formula:

//Named Date Difference
whileprintingrecords;
numberVar dur := datediff("s",{@Date/Time Created}, {@End Date/Time}); //get the seconds between 2 dates
numbervar days;
numberVar hrs;
numberVar min;
numberVar sec;
stringVar ddhhmmss;
days:= Truncate(Truncate(Truncate(dur/60)/60)/24);
hrs := Remainder(Truncate(Truncate(dur/60)/60),24);
min := Remainder(Truncate(dur/60),60);
sec := Remainder(dur,60);

ddhhmmss := totext(days,0,"") + ":" + totext(hrs,"00") + ":" + totext(min,"00") + ":" + totext(sec,"00");

ddhhmmss

I saw Lbass respond to an earlier thread, saying to use this formula to get it back into minutes then use the above formula and have dur be the average formula. But every time I do that, it says that my average formula cannot be summarized. Any suggestions?

Formula I'm using to convert the above back to minutes:

//named Average
stringvar array y := split({@Date Difference},":");
val(y[1])*1440+
val(y[2])*60+
val(y[3])

Any help would be appreciated. If you need more information, please let me know.

Thanks,
Ryan

 

[lbass]

I think you have misunderstood. Do your calculation for average using seconds. Then once you have the result, plug that into the formula that converts it to the string.

-LB

 

[Ohioste]

If so, I'm still a little confused.

So for example, my formula to get DD:HH:MM:SS is this:

whileprintingrecords;
numberVar dur := datediff("s",{@Date/Time Created}, {@End Date/Time}); //get the seconds between 2 dates
numbervar days;
numberVar hrs;
numberVar min;
numberVar sec;
stringVar ddhhmmss;
days:= Truncate(Truncate(Truncate(dur/60)/60)/24);
hrs := Remainder(Truncate(Truncate(dur/60)/60),24);
min := Remainder(Truncate(dur/60),60);
sec := Remainder(dur,60);

ddhhmmss := totext(days,0,"") + ":" + totext(hrs,"00") + ":" + totext(min,"00") + ":" + totext(sec,"00");

ddhhmmss

I'm grouping this by a date and so there are multiple records. I'd like to see the average of this. So I use this formula to convert the above time to seconds:

//named average
(stringvar array y := split({@Date Difference},":");
val(y[1])*1440+
val(y[2])*60+
val(y[3]))*60

I will not let me summarize @average. It always gives me the error that the field cannot be summarized.

 

[Ohioste]

Any help would be appreciated.

Thanks,
Ryan

 

[lbass]

You should be creating an average formula using this formula (let's call it {@secs}):

datediff("s",{@Date/Time Created}, {@End Date/Time})

Then your average becomes:

average({@secs},{table.groupfield})

...if you are doing this at the group level. Then if you want to display the result as a string, use your previous formula, but for 'dur' use:

numberVar dur := average({@secs},{table.groupfield});

-LB

 

[Ohioste]

Lbass,

I just got a chance to actually complete this. Your formula works great! It's much appreciated!

I'm taking Crystal Classes in October, so hopefully when I come on here I can help people out!

Thanks,
Ryan