How can I add BUSINESS HOURS and get a date? 2

[dscheste]

Hello gurus, hope some of you guys have already run across this one.

In Crystal we can easily calculate the amount of business hours between two different dates.

There is even a way to work with negative values.

I have a problem of another kind.

I have to ADD several business hours on a regular basis to dates.

So

if I_have={start_date} and I_have={end_date} then
I_can_calculate_the_amount_of_business_hours_between_them

else

Am_I_screwed?

Well. I have start date only and have to ADD 2 business days fo this date. (The start date might not fall into the working hours bracket).

Any ideas?

Thank you

dscheste

 

[lbass]

Ken Hamady's faq767-995 might help you. See common formula #17 for adding business days to dates.

-LB

 

[dscheste]

LBASS - thank you very much for the link to the formula. The site is really interesting.

However, the formula does not refer to my problem as it adds or substracts business days. I need hours.
2 busines days = 16 business hours.

To illustrate what I am talking about here are some details:

Fox example:

A ticket came in at 9:15 25/7/2004

if I add 2 business days, I would have SLA at 00:00 27/7/2004


but I have to have 17:00 27/7/2004 as we start at 9 and finish at 17.

See what I mean?

At the moment I have a matrix in excel with formulae calculating different options and then another formula, which picks the right matrix segment for the report.

 

[pandpp]

dscheste,

Logic would dictate that if you added 2 business days to
20040725 0915
then I would get
20040727 0915
and NOT
20040727 1700
which is MUCH nearer 3 business days than 2.

As such, it sounds like you have to abide by more rules than you have listed here.
Might I suggest you list all the rules? That way, we can truly work on providing you a solution that WILL work for you.

Peter.

 

[dscheste]

Hello Peter

thank you for constructive criticism, you are partially right.

In the prefatory remarks I have mentioned, that I have to add business hours, not business days.

So we have to have some variables:

1. Start_of_business_day, (HH:MM:SS) which may very.
2. End of business day, (HH:MM:SS) which may very as well
3. Business_day, which may define business days, as for some instances business days might be Friday through Wednesday, for example.

At the very moment I solve the problem by a matrix of values in excel, where another formula governs the matrix and picks values for a partivular instance while Crystal does substractions.

Right now I am not conserned with the definition of Business_day, I may consider Business_day as CR does by default - Monday to Friday.

As to the logical example:

Logic would dictate that if you added 2 business days to
20040725 0915
then I would get
20040727 0915
and NOT
20040727 1700
which is MUCH nearer 3 business days than 2.

The 2004/07/25 09:15 occurs on Sunday, this is why the ticket has to wait until next business morning and then the 2 business days would be 16 business hours, which will be over by 2004/07/27 17:00 exactly given the Start_of_business_day=09:00 and End_of_business_day=17:00


I hope this clarifies the challenge I am facing.

Hoping for cooperation I appreciate your input.

dscheste

 

[dscheste]

bump

 

[pandpp]

dscheste,

Let's try another example then please, just so I can understand your expected result.

A ticket comes in at:
20040728 1212 (Wednesday)

If you add 2 business days to that, what are you expecting as your result?
20040730 1212 (Friday) OR 20040729 1700 (Thursday)?

Peter.

 

[dscheste]

Hello Peter

A ticket comes in at:
20040728 1212 (Wednesday)

If you add 2 business days to that, what are you expecting as your result?
20040730 1212 (Friday) OR 20040729 1700 (Thursday)?

Ok. the ticket came in at 12:12, the end of business day is 17:00, than means that the ticket has been worked on for 4:48 (17:00 - 12:12).

We have 2 business hours, or 16 business hours for the ticket, so 16 - 4:48 already spent would be 11:12 business hours to go for that ticket.

That leaves us with Friday 12:12 as the end of 16 business hours.


Another example:

1. Ticket in at Wednesday 05:00 - +16 business hours would be Thursday 17:00 as the ticket was worked on the whole Wednesday and Thursday

2. Ticket comes in at Wednesday 18:00 - +19 business hours would be Friday by 17:00 as there was no business time spend on the ticket on Wednesday.

Hope this clarifies.

 

[dscheste]

bump

 

[lbass]

I spent quite a bit of time working on a formula before realizing exactly how time-intensive this would be--probably more time than anyone here can donate. The following resource probably provides the best assistance:

http://support.businessobjects.com/communityCS/TechnicalPapers/cr_business_days_hours.zip.asp

-LB

 

[dscheste]

Hello LBass, thank you for your input.

I have this one:

http://support.businessobjects.com/communityCS/TechnicalPapers/cr_business_days_hours.zip.asp

And it works just fine substracting the hours and calculating the difference.

Anyways, thank you for your help.

Last question:

I have a CRM with SQL holding the database, what would be your advise as how to link the CRM database and Crystal for some reports, running direct queries through Crystal at the SQL or rather working with XML export of the SQL database?


Thank you in advance.

dscheste

 

[sdebo2]

Below is some code that may help you. It is a modification of code I use to increment by business days,
so it may not be perfect, but you’ll get the point. It is a for loop that will add one hour each time
it loop, for the duration of the AddBusiness varible. After adding the hour it check to see if the new
time is part of the Business Hours (as provide by the rule), and if not increments the time to the next
business hours. Currently the rule are hard coded, Monday – Friday 8am to 5pm, but you could add some
more variables if the business hours change.

(@SLAEndTime)
NumberVar AddBusiness; //Number of Hours to add
DateTimeVar SLAStartTime; //Time to start
DateTimeVar SLAEndTime; //End Time after adding business hours
numberVar I; //Counter


SLAEndTime:=SLAStartTime;

for I :=1 to AddBusiness do // Represents the SLA in Days used only as reference
(SLAEndTime:=SLAEndTime+(1/24); // Add one Hour
//Check for Before Business Hours
if(Time(SLAEndTime)<Time(08, 00, 00))
then (SLAEndTime:=DateTime(Date(SLAEndTime), Time(08, 00, 00)));
//Check for After Business Hours
if(Time(SLAEndTime)>Time(17, 00, 00))
then (SLAEndTime:=DateTime(Date(SLAEndTime)+1, Time(08, 00, 00)));
//Check for Saturday
if(DayOfWeek(SLAEndTime) in [7])
then(SLAEndTime:=SLAEndTime+2) ;
//Check for Sunday
if(DayOfWeek(SLAEndTime) in [1])
then(SLAEndTime:=SLAEndTime+1);

//Check for Holidays
if(SLAEndTime in [date(2004,01,01),
date(2004,05,31),
date(2004,07,04),
date(2004,09,06),
date(2004,11,25),
date(2004,12,25),
date(2005,01,01)])
then (SLAEndTime:=SLAEndTime +1)
)


Hope this helps

 

[dscheste]

sdebo - thank you for sharing such a nice code.

I was thinking of this approach - just adding a fixed increment to the time given, but I decided against it as this will provoke 2 such incremental calculations for every case I process and there are several hundreds of cases to calculate.

I have looked at your code, neat and vivid.

I have played around with it, it does not seem to slow down the calculations. Although I was running it in "boleans mode" only.

Could you please share with me your experience in tying the formula into the report, any particularities?

Do you operate with priorities, where the "AddBusiness" changes depending on the Priority?

I have already posted a note about the manual business hours difference formula for CRm which does not return right amount of BH if start and stop are the same day before or after working hours.

 

[sdebo2]

I am using a variation of the “For Loop” above in several Monthly reports. Mine increment by Day instead of by hour. These are used to determine the Maximum time allowed to complete a problem. The Time allowed, in my report, is dependent on the Priority/Severity of the ticket. The Report is grouped by Severity. You could also set the “AddBusiness” variable with an If Statement or Case statement just prior to the “For Loop” if you did not want to group the report by severity.

The formula has been working well for me for several months.