Average Time Spent(Hours, Minutes,Seconds).

[surad]

I need a formula for in Crystal Reports 8.5 for the average time spent per employee on diferent types of activities in (Hours, Minutes,Seconds).

Thanks,
Yuri

 

[mbarron]

I'm assuming you have two fields that are start and end datetimes.

Try this one:

numbervar secs;
numbervar hr;
numbervar min;
numbervar sec;

secs:=datediff("s",{datetime first},{datetime second});


hr:=truncate(secs/3600);
min:=truncate((secs/3600-hr)*60);
sec:=(((secs/3600-hr)*60)-min)*60;
totext(hr,0)&"h"&totext(min,0)&"m"&totext(sec,0)&"s" Mike

 

[surad]

Thanks Mike it works, except I am getting "0h0m21s". I would like to get the following output "00:00:21".

Yuri

 

[mbarron]

Chane this:
totext(hr,0)&"h"&totext(min,0)&"m"&totext(sec,0)&"s"
to:
totext(hr,"00&quot&":"&totext(min,"00&quot&":"&totext(sec,"00&quot Mike

 

[surad]

Thanks Mike - After using Calculion you provided on last e-mail, I am having difficulty showing subtotal and total for activities.

Thanks,
Yuri

 

[mbarron]

If you are trying to summarize on the formula I supllied, you'll be limited to Max and Min and a couple others. This is because the field result is a string. In order to get summaries such as totals and averages (and any other that require a number) you need to use the number of seconds to create the summary and then use that summary for the secs:= part of the formula.

E.g. if you had a grand total of seconds the line in the fornula would looks something like this:

secs:=sum({seconds.field}) Mike

 

[surad]

Mike,

This is what I currently have (I am sure where secs:=sum({seconds.field}) would be difined) -

numbervar secs;
numbervar hr;
numbervar min;
numbervar sec;

secs:=datediff("s", {Main.START_TIME}, {Main.END_TIME});


hr:=truncate(secs/3600);
min:=truncate((secs/3600-hr)*60);
sec:=(((secs/3600-hr)*60)-min)*60;
totext(hr,"00&quot&":"&totext(min,"00&quot&":"&totext(sec,"00&quot

 

[mbarron]

Are you trying to summarize on the formula I supplied?
Mike

 

[surad]

Yes

 

[mbarron]

You can't do a summary of the time using my formula. It is for display only. If you don't already have this formula field, create it:
datediff("s", {Main.START_TIME}, {Main.END_TIME})

You can suppress the field on your canvas. Do the summary operation on the field. Insert the summary into the formula I supplied


numbervar secs;
numbervar hr;
numbervar min;
numbervar sec;
//summary of seconds goes here
secs:=summary({seconds})


The rest of the formula........ Mike

 

[surad]

Mike,

This line secs:=summary({seconds})as part of the formula doesn't work. I also have this line datediff("s", {Main.START_TIME}, {Main.END_TIME})
as a formula but I am having proplem how to apply all together.

Your assistance is greatly appreciated.

Thanks,
Yuri

 

[mbarron]

Create this formula call it seconds and insert it into your details line:

datediff("s", {Main.START_TIME}, {Main.END_TIME})

Insert a summary for the formula.
If want to find the total seconds for all of the details this line:
secs:=summary({seconds})
would be:
secs:=sum({@Seconds})

If that doesn't explain it well enough, send the report to me, with data and I'll see if I can help you out that way. We may have our wires crossed completely. I may be trying to give you the wrong solution to your problem

If you are using CR9, don't send it. I can't open the file.

mbarron"@"arkwright.com Mike