resolution time in business hours only 2

[MJD500]

Hi,

I did search tek-tips for answers but was not able to find anything BUT if the answer already exists, could someone guide me to the thread please?

I have

Work Start (Datetime)
Work Stop (Datetime)

When a resolver group starts working on a ticket, the datetime gets stored in the Work Start and when the ticket is resolved, the datetime gets stored in the Work Stop

To calculate the resolution time, I simply do Work Stop - Work Start BUT if the resolver starts working on the ticket at 4:05PM and resolves it the next day at 8:30, I would like the resolution time to show 55 minutes because the business hours are 8:00 to 5:00 PM.

How do I do this?

I would also like to do the same for the weekend (not count the weekend) and holidays.

Can someone help?

Thank you

Jocelyne

 

[MJRBIM]

Ken has an FAQ at -

http://www.kenhamady.com/form13.html

 

[MJD500]

Thank you. I knew the solution had to be somewhere. That is exactly what I was looking for.

Jocelyne

 

[seabubble]

Hi MJD500, I have been looking into this for one of my reports and have found myself using the formula you found. I would like to ask what you have done with the minutes. I am trying to get the report to show days hours and minutes, the above formula does not add the minutes. Have you found anywhere that has the formula to do this.

 

[MJD500]

Hi Seabubble,

Actually, I'm having the same issue. I need to calculate the result in minutes also. I wonder if someone else can help?

Jocelyne

 

[lbass]

The Business Objects website has a selection of papers that might be helpful:

http://support.businessobjects.com/...x_ver=&index_lang=&indexq2=&indexq3=&indexq4=

-LB

 

[seabubble]

Hi Jocelyne,
Wow that was quick! It's the first time I have used this site and I am very impressed, thanks for getting back so fast. Thanks LB for the link. I am now reading the 54 page solution!:0)

 

[seabubble]

Hi again, I have read the papers. It mentions cruflwdays.dll is included in the download of cr_business_days_hours but it is not. I have searched the net and Business Objects site but can not find this file. I will let you know if I do.
Steve.

 

[MJD500]

Hi,

I am trying to save following formula and I am getting an error message: The formula cannot be evaluated at the time specified. I can't figure out what I did wrong.

Can someone help?
//Main formula
WhileReadingRecords;
Local DateVar Start := date({SRMIS_REQUEST.CREATE_DATE}); // place your Starting Date here
Local DateVar End := date({SRMIS_REQUEST.DATE_WORK_STOP}); // place your Ending Date here
Local NumberVar Weeks;
Local NumberVar Days;
Local Numbervar Hol;
DateVar Array Holidays;

Weeks:= (Truncate (End - dayofWeek(End) + 1
- (Start - dayofWeek(Start) + 1)) /7 ) * 5;
Days := DayOfWeek(End) - DayOfWeek(Start) + 1 +
(if DayOfWeek(Start) = 1 then -1 else 0) +
(if DayOfWeek(End) = 7 then -1 else 0);

Local NumberVar i;
For i := 1 to Count (Holidays)
do (if Holidays in start to end then Hol:=Hol+1 );

Weeks + Days - Hol;


//Holiday Listing formula to go into the report header of the report.
BeforeReadingRecords;
DateVar Array Holidays := [
Date (2003,12,25),
Date (2003,12,31)
];
0

Thanks
Jocelyne

 

[rosemaryl]

Dear MJD500,

You cannot use BeforeReadingRecords or WhileReadingRecords in these formulas.

These two passes (read up more by going into help and reading the information when searching on pass).

Records at BeforeReadingRecords can only be done for those formulas that can be calculated without reading records. Math like 1 + 1 and Text formulas that do not reference db fields.

WhileReadingRecords phase are items that can be performed while returning the records.

Change those two statements to be WhilePrintingRecords;

ro



Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[rosemaryl]

Dear md500 ...

My apologies ... I just went and actually read through theformulas. You can use BeforeReadingRecords in the Holiday one because no db records should be read. In ken's formula you will note that it is just setting the value of the variables. So that is fine and not the issue, unless you referenced some db field in the formula.

The issue must be with the Whilereadingrecord which should also be fine. Where are you placing the formulas? Are you placing where indicated by ken's formulas?

Try leaving the holiday array formula as beforereadingrecords and changing just the business hours formula to whileprintingrecords. What happens?

I have to leave to teach class so won't be able to check back until evening ...

regards,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[MJD500]

Very good Rosemary.

I changed just the business hours formula to whileprintingrecords and left whilereadingrecords for the holiday formula and I put the holiday formula in the report header as suggested by Ken. It works.

Now, I have to be able to display the result in the following format HH:MM:SS. Ken's formulas are broken up into Hours or days. Does anyone know of a formula which would help me?

Thanks

Jocelyne