Need to find average of datediff and display in HHMM

[fennerhillpro]

I am a Newbee and working on Ver 9. I need to get the time diff between time in and time out which the formula below works fine for. However, I also need to find the aveage and mean times and display and since they are a string I can't. I can display in sec or Min (by adding /60) but Icannot figure out how to then convert back to time so it displays back to 7:12.

numberVar dur := datediff("s",{@admitdatetime},{@disch datetime}); //get the seconds between 2 dates
numberVar hrs;
numberVar min;
stringVar hhmmss;
hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
hhmmss := totext(hrs, "0&quot + ":" + totext(min, "00&quot ;

hhmmss


Thanks,
Paul

 

[rosemaryl]

Dear Paul,

You will need to base each formula on your datediff in seconds.

So one formula will convert that to display as time.

One formula will sum the values of the formula that returns the diff in seconds, which you will then divide by the number of records to get the average .

And one formula would be created to get the mean.

In each formula you would then take the value returned and use your formatting as time. Here is my formula for displaying as time, I like DD:HH:MM:SS since my times on reports can span many days.

As you can see in my example below, I am calculating the average of the seconds returned by my datefiff formula based on a group in the report.

You would replace the reference for total seconds with your formula name and group field or your formula that performs the averaging!

Local NumberVar TotalSec := Average ({@Age in Seconds}, {Incident.Open Date & Time}, "daily&quot ;
Local NumberVar Days := Truncate (TotalSec / 86400);
Local NumberVar Hours := Truncate (Remainder ( TotalSec,86400) / 3600);
Local NumberVar Minutes := Truncate (Remainder ( TotalSec,3600) / 60);
Local NumberVar Seconds := Remainder ( TotalSec , 60);

Totext ( Days, '00', 0,'') + ':'+
Totext ( Hours, '00', 0,'') + ':'+
Totext ( Minutes,'00', 0,'') + ':'+
Totext ( Seconds,'00', 0,'')

Hope that helps,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[OKCMikeSoll]

Rosemary,

Your suggestions were very helpful to me. I have one problem though.. It is giving me the average open time of the problem tickets for each day. I need to get an average problem ticket time by assignment group. I have a group in the report for {PROBSUMMARYM1.ASSIGNMENT} and when I insert the formula you listed above it gives me the same average for each group. But it changes for each day. I hope this is not confusing, if you need more information please let me know.

Thanks,
Mike

 

[rosemaryl]

Dear Mike,

First, I assume that you have a formula for age in seconds in the detail section of your report.

Now, whenever you create a group ... you can perform summary functions specific to that grouping.

Sum({@formula or field},Conditon}

In the above example Condition means Group.

Because date/time fields can be grouped by Year, Month, Day, Hour, Seconds, .... you get an additional option to indicate the "Order By".

Sum({@formula or field},Conditon,'order')

Sum({@age},{Table.DateTime},"daily")

Now, let's take your example and assume that you have a formula called {@age} in the detail section that is performing a datediff in seconds using your start and end dates.

Local NumberVar TotalSec := Average({@Age in Seconds},{PROBSUMMARYM1.ASSIGNMENT});
Local NumberVar Days     := Truncate (TotalSec / 86400); 
Local NumberVar Hours   :=  Truncate (Remainder ( TotalSec,86400) / 3600); 
Local NumberVar Minutes :=  Truncate (Remainder ( TotalSec,3600) / 60); 
Local NumberVar Seconds :=  Remainder ( TotalSec , 60); 
 
Totext ( Days,   '00', 0,'') + ':'+ 
Totext ( Hours,  '00', 0,'') + ':'+ 
Totext ( Minutes,'00', 0,'') + ':'+ 
Totext ( Seconds,'00', 0,'')

Does that make sense to you?

Regards,

ro



Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.