### Symptom

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?

### Resolution

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.

### Keywords

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