1212932 - Calculating the average time for DateTime/Time data type fields

A Crystal Report returns a list of datetime or time records in the details section.

How can you calculate the average time for all these records?

To calculate the average time for a list of datetime or time fields, you must first create a formula to convert the time portion of the datetime or time field to display the total seconds for the field.

Take this formula and perform an average calculation on it. This returns the average time in seconds for the datetime or time field.

Create a second formula to display this average as a time in hours, minutes, and seconds.

To calculate the average time of a datetime or time field:

1. Create a formula to calculate the total seconds for the time portion of the datetime or time field. Call this formula @seconds.

// @seconds

// This formula converts the hour and minute portions of the field into seconds.

// It then adds the converted hour, minute and second portion of the field together.

(hour({datetime.field }) * 60 * 60) + (minute({datetime.field }) * 60) +

(second({datetime.field }))

2. Place the @seconds formula in the details section of your report.

3. Create a grand total average summary based on the @seconds formula.

4. Create a second formula and call it @averagetime.

//@averagetime

//the @averagetime formula calculates the average time between a number of

//datetime or time fields.

numbervar TotalSeconds := Average ({@Seconds}, {table.GroupField});

//now declare 4 more variables:

numbervar RemSecs;

numbervar HH ;

numbervar MM;

numbervar SS;

//HH variable holds the hour(s) and truncates the decimal portion.

HH := truncate(TotalSeconds / 3600);

//RemSecs calculates the seconds left after subtracting the hour(s).

RemSecs := TotalSeconds - (HH * 3600);

//MM variable holds the minute(s) and truncates the decimal portion.

MM := truncate(RemSecs/60);

//SS variable holds the seconds remaining after subtracting all the hours and minutes

SS := TotalSeconds - (HH * 3600) - (MM * 60);

//The time function in Crystal Reports requires whole numbers so we round each

//variable just in case there are decimals left over from the previous calculations.

time(round(HH), round(MM), round(SS))

5. Place the @averagetime formula in your report footer.

The @averagetime formula displays the average time of your datetime or time fields in an hour, minute, second format.

FORMULA FORMULAS AVERAGE DATETIME TIME DATE AVG Seagate Crystal Reports Display the average time of a datetime or time field Seagate Info 7.5 , c2008475 , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To

SAP Crystal Reports 10.0 ; SAP Crystal Reports 8.0 ; SAP Crystal Reports 9.0 ; SAP Crystal Reports XI ; SAP Crystal Reports XI R2