Avg. Time Formula
Post Author: cjsmile2106
CA Forum: Formula
Hello Everyone,
I have two formulas that I'm working with to create an average time between two times (same field) in a hh:mm:ss format. My first formula appears to be working correctly. Its calcualating the difference in minutes (number format). Then my second formula is attempting to average it and put in in a time format. It appears the first is working correctly, when I place it on my report it displays and calcualtes correctly. However when I place my second formula (the average) in the report I recieve and error stating that my first formula can not be calculated "my month must be between 1 to 12". I double and tripled checked and all of my dates are in between 1 and 12. The only thing I can think of is some of my dates are null which results in a null value for my first formula. Wierd thing is that when after I place my first formula in the report and I right click to do an insert it only give me and option of "Field Heading". I'm using CR XI. Any suggestion is greatly appreciated!! Below are my formulas:
First Formulat to calculate the difference in minutes:
NumberVar TotHours;NumberVar TotMins;NumberVar TotSecs;NumberVar Hours;NumberVar Mins;NumberVar Secs;NumberVar QSeconds; NumberVar LSeconds; NumberVar YY;NumberVar MM;NumberVar DD;DateVar QDate; DateVar LDate; NumberVar TotMinutes;
TotHours := ToNumber({@quinstime}[1 to 2]);TotMins := ToNumber({@quinstime}[4 to 5]);TotSecs := ToNumber({@quinstime}[7 to 8]);QSeconds := (TotHours * 60 * 60) + (TotMins * 60) + TotSecs;
TotHours := ToNumber(next({@quinstime})[1 to 2]);TotMins := ToNumber(next({@quinstime})[4 to 5]);TotSecs := ToNumber(next({@quinstime})[7 to 8]);LSeconds := (TotHours * 60 * 60) + (TotMins * 60) + TotSecs;
TotMinutes := (LSeconds - QSeconds) / 60; YY := ToNumber({@quinsdate}[1 to 4]);MM := ToNumber({@quinsdate}[6 to 7]);DD := ToNumber({@quinsdate}[9 to 10]);QDate := Date(YY,MM,DD);
YY := ToNumber(next({@quinsdate})[1 to 4]);MM := ToNumber(next({@quinsdate})[6 to 7]);DD := ToNumber(next({@quinsdate})[9 to 10]);LDate := Date(YY,MM,DD);
TotMinutes := TotMinutes + (((LDate-QDate)24)60) ;TotMinutes;
Second Formula to calc the average and format:
NumberVar TotHours;NumberVar TotMins;NumberVar TotSecs;NumberVar Hours;NumberVar Mins;NumberVar Secs;NumberVar QRSSeconds;NumberVar RSTSeconds;
TotSecs := ((Average ({@NumberTime}, {FL.State}))*60);
TotHours := TotSecs;if TotHours / 3600 > 0 Then Hours := Truncate(TotHours / 3600)Else Hours := 0;
TotMins := TotHours - (Hours * 3600);If TotMins / 60 > 0 Then Mins:=Truncate(TotMins / 60)Else Mins := 0;
Secs := (TotMins - (Mins * 60));
if Hours < 10 Then StringVar GHH := "0"+ ToText(Hours)else StringVar GHH := ToText(Hours);if Mins < 10 Then StringVar GAA := "0" + ToText(Mins)else StringVar GAA := ToText(Mins);if Secs < 10 Then StringVar GSS := "0" + ToText(Secs)else StringVar GSS := ToText(Secs);
GHH[1 to 2]":"GAA[1 to 2]":"GSS[1 to 2];