Business Hour Formula

[SchuhTL]

I am trying to implement Ken Hamady's Business Hours Formula and I have run into a problem. I am a member of a reporting team that is over a Call Center. My {Start.Time} is the create date/time of a ticket and my {End.Time} is the date/time the ticket was resolved. This is a 24hr call center so tickets are being created and resolved around the clock. Most of our fix agents(people who resolve the tickets) only work 9-5. It seems the formula works great except when a ticket was created or resolved outside of the fix agents business hours. The formula returns a negative value. For example I have a ticket that was created on 08/20/01 7:40:08AM and Resolved 08/20/01 9:41:03AM. The business day(from Ken's Business Days Formula) shows correctly as 1 day however the Business Hours shows -21.98. I am using Crystal Reports 8.0 Developer Edition. Please let me know if I need to provide any additional information.

//Business Hours Formula
WhilePrintingRecords;
NumberVar Days := {@Business Days Formula}; // The field that calculates your business days
TimeVar SetStart := TimeValue( "8:00" // The start your work day
TimeVar SetEnd := TimeValue("17:00" // The end your work day
TimeVar StartTime := TimeValue({Start.Time});// The data field that holds your Start Time
TimeVar EndTime := TimeValue({End.Time}); // The data field that holds your End Time

Days * ((SetEnd - SetStart) / 3600)
- ((SetEnd - EndTime) / 3600)
- ((StartTime - SetStart) / 3600)

 

[kenhamady]

If one is closed 2 hours after the end of the day, do you want to count those 2 hours? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[SchuhTL]

No I only want to count hours between 9-5.

 

[kenhamady]

Try adding the following lines after line 6:

If EndTime > SetEnd then EndTime:= SetEnd;
If StartTime < SetStart then StartTime := SetStart;

This will work if the start is at least after midnight.
If you have the possibility of starting something the previous evening (ie, like at 8pm) you will have to add more logic to make that adjustment. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[SchuhTL]

Thanks for your help Ken! I do have start dates before midnight any suggestions?

 

[kenhamady]

It can be done, but it will be a bit more messy. It will require changes (an if-then-else test) in the 'days' formula as well. This is because you are adjusting the date as well as the time. It might make sense to create two formulas first that are:

Adjusted Start Date/Time
Adjusted End Date/Time

In these you do all of the logic to calculate the Date/Time you want my formulas to consider as the Start and End. Then pass these to my formulas. So a 6pm start would be adjusted to the next day at 9am. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com