Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Average of a time field CR9 1

Status
Not open for further replies.

TimBiesiek

Programmer
Nov 3, 2004
151
AU
Hi All,

Using CR9.

Am trying to get the average start time and finish time for a group of workers.

Have a field (RTime) that gives a whole lot of activity times, and am using the min and max of this, grouped by an RDate field to get the start and finish times for each user for each day.

I then want to average the start time and finish time for each team of users, but can't get a summarise the Min or Max RTimes...

Any help?

Thanks!!!
pcguru.gif
 
I think you will first have to convert your minimum (and maximum) times to seconds in a formula like {@secs}:

DateDiff ("s", DateTime(0,0,0,0,0,0), DateTime(Date(0,0,0),minimum({@time},{table.user})))

This assumes you have a group on {table.user}.

Then use a variable to accumulate the results:

//{@accum} to be placed in the group header or footer:
whileprintingrecords;
numbervar secs := secs + {@secs};

//{@ave} to be placed in the report footer:
whileprintingrecords;
numbervar secs;

secs/distinctcount({table.user})

You can then convert the average seconds back to a time format by using a formula like that found in FAQ767-3543.

-LB
 
Hi LB,

Tried your formula above, changing it to fit my tables:
Code:
DateDiff ("s", DateTime(0,0,0,0,0,0), DateTime(Date(0,0,0),[red]minimum({cur_FieldResult.RTime},{cur_FieldResult.RDate}[/red])))

but it says a time is required here... And the minimum expression is highlighted....

Any thoughts?
 
Ah, just got it with a ctime().... Will let you know how I get on with the rest of it!
 
LB, you're a champ! Have a star! Works a charm now!
 
Hmm, I run into troubles though, when I am trying to convert 271440 seconds, back into a time...

It ends up being 75:24:00.... either this is some time in the middle of the night when I'm fast asleep, or the conversion bit can't handle that many seconds...

Or, perhaps, my averaging formula isn't working right...
 
Not sure how you want to display the days, but try the following:

numberVar dur := DateDiff ("s", DateTime(0,0,0,0,0,0), DateTime(Date(0,0,0),ctime(minimum({cur_FieldResult.RTime},{cur_FieldResult.RDate}))));
numberVar days;
numberVar hrs;
numberVar min;
numberVar sec;
stringVar ddhhmm;

days := Truncate(dur/86400);
hrs := truncate(remainder(dur,86400)/3600);
min := truncate(remainder(dur/60,60));
sec := remainder(dur,60);

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

-LB
 
Thanks LB!

It turns out my averaging formula wasn't right.... it wasn't dividing by the right number.... got it sorted now though! Cheers!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top