Average Ticket Age in seconds for Buisness Hours only

[OKCMikeSoll]

I have looked through the threads for this issue and have found a few formulas that help but I am still running into a problem.

In my report I have a formula (@TimeDiff) that gives me the DateDiff in seconds of the {TABLE.OPEN_TIME},{TABLE.CLOSE_TIME} then I have a formula (@AverageAge) that gives me the average age in seconds for the assignment group of the ticket then it converts the seconds to Days/Hours/Mins/Secs. This all work great, but now I need the second formula (@AverageAge) only give the average age within our buisness hours (M-F 8:00AM - 5:00PM) but still in the format Days/Hours/Mins/Secs. Listed below are the formulas that I am using.

@TimeDiff
DateDiff("s",{PROBSUMMARYM1.OPEN_TIME} ,{PROBSUMMARYM1.CLOSE_TIME})

@AverageAge
Local NumberVar TotalSec := Average ({@TimeDiff},{TABLE.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,'')


I have tried to use Ken Hamady's formula to calculate Work Days/Hours and it works but it just gives me the number of buisness hours and I cannot convert that to my Days/Hours/Mins/Seconds. Is there a way to get the DateDiff of buisness hours only but in seconds?

Sorry for the novel =) Any help would be most appreciated!

Thanks
Mike Soll

 

[rosemaryl]

Dear Mike,

Multiply the result of Ken's formula by 3600 to make it seconds .. .there are 3600 seconds in an hour.

@AverageAge
Local NumberVar TotalSec := (@KensWorkHoursFormula *3600) ;
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,'')

Regards,

ro


Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.