Has anyone calculated an average time before? I have a Date/Time field (HYYMDS) and am able to determine the start and stop time for each day, but from that I need to get an average for the week. I'm stumped.

This message has been edited. Last edited by: Joni Campbell - Europa Sports,

I'd probably convert to seconds, find the average, then convert back to the desired Date/Time format.

Thanks, Francis. I translated to seconds, found the average, and then translated the number of seconds which represents the average into hours and minutes, manually translated out of military time and determined if the time was am or pm, then concatenated all of the parts together. In researching the date reformatting, I found the formats of HHISa, HHIa, and HHIS, which I'd never seen before; they convert a date/timestamp into time only; the "S" includes the seconds in the time, and the "a" will include am/pm.

 DEFINE FILE HOLD02
-* HTIME used to translate from Date/Timestamp (HYYMDS) format to the # of seconds since midnight
AVE_IN_SECS/D12.2=HTIME(8, MIN_LOGIN, 'D12.2');
-* I found that I needed to divide the # of seconds by 1000 … ?
AVE_SECS2/D9=AVE_IN_SECS / 1000 ;
END
TABLE FILE HOLD02
SUM 
	 AVE.AVE_SECS2  NOPRINT
COMPUTE IN_HOURS/I6 = AVE.AVE_SECS2 / 3600; NOPRINT
COMPUTE HOURS_PM2AM/I2 = IF IN_HOURS GT 12 THEN IN_HOURS - 12 ELSE IN_HOURS ; NOPRINT
COMPUTE AM_OR_PM/A2 = IF IN_HOURS GT 12 THEN 'pm' ELSE 'am' ; NOPRINT
COMPUTE INHRS_RMNDR/I4 = IMOD(AVE.AVE_SECS2, 3600, INHRS_RMNDR); NOPRINT
COMPUTE IN_MINUTES/I2 = INHRS_RMNDR / 60;  NOPRINT
COMPUTE FP_TIME2/A7 =  FPRINT(HOURS_PM2AM, 'I2', 'A2') || ':' || EDIT(IN_MINUTES) || AM_OR_PM ;

BY  AGENTLASTNAME AS 'Last,Name'
BY  AGENTFIRSTNAME AS 'First,Name'

ON TABLE PCHOLD FORMAT EXL07
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END
 

Joni, HTIME provides the result in milliseconds.

of course you could cheat...
calculate the median
i do that whenever i have a vector that is sortable into some sort of order, but not really averagable..
you count the frequency of each occurence
cumulative of that frequency
then Cumulative%
and when that cumulative % hits 50%, bingo, you have your median.

time     Sum of obs cume cume%
7:10 AM	1         1       3%
7:20 AM	1         2       7%
7:30 AM	1         3       10%
7:40 AM	1         4       14%
7:50 AM	3         7       24%
8:01 AM	1         8       28%
8:03 AM	1         9       31%
8:05 AM	1        10       34%
8:09 AM	5        15       52% ***
8:39 AM	3        18       62%
9:05 AM	1        19       66%
9:10 AM	1        20       69%
9:15 AM	1        21       72%
9:20 AM	1        22       76%
9:25 AM	1        23       79%
9:30 AM	1        24       83%
9:35 AM	1        25       86%
9:40 AM	1        26       90%
9:45 AM	1        27       93%
9:50 AM	1        28       97%
9:55 AM	1        29      100%
Grand Total       29		

I had to do this with colors, once. no lie!
a vector of RGB values.
worked.
silly, but worked.