Calculating Business Hours 2

[raja2008]

thread767-1436403

Hello LB and everyone!

Using Crystal XI R2 and Remedy.

I have the same problem as this thread.

I have to calculate the business hours ,but for us tickets can come in 24*7 and 365 and also they can be closed 24*7 365,But user wants to exclude the weekedns ,holidays and after hours.


I used your method of moving the ticket to next day if they are opened or closed after hours.
Now my problem is when i move them to nextday when they fall into wwekend or holiday,i'm having trouble.
I'm getting negative values.
Please help.

I did exactly as in KenHammady and used your formula to move them to next day.
//MOving the HQ-Open date to next day if it opens after business hours.
if time({HPD_HelpDesk.HQ_Open}) > time(17,0,0) then
datetime(date(dateadd("d",1, {HPD_HelpDesk.HQ_Open})),time(8,0,0)) else
if time({HPD_HelpDesk.HQ_Open}) < time(8,0,0) then
datetime(date({HPD_HelpDesk.HQ_Open}),time(8,0,0)) else
{HPD_HelpDesk.HQ_Open}

Please help.

 

[raja2008]

Hello Everyone!

I got this to work.

I used Ken Hamady's formula's and LB's formula's

Here is what i did.
I have tickets coming in over weekend ,holidays,afetr hours.
So,i had to manipulate the formula's.
//Formula #1 To move the start date to next day if it is afetr work hours and move to business open time if it comes before .

if time({startdate}) > time("5:00:00PM") then
datetime(date(dateadd("d",1, {statrdate})),time(8,0,0)) else
if time({startdate}) < time(8,0,0) then
datetime(date({startdate}),time(8,0,0)) else
{startdate}


//Formula # 2
To add Business day to the startdate if it falls in Holiday or weekend


WhilereadingRecords;
DateVar Array Holidays;
DatetimeVar Target:=datetime(date({@formula1}),time(8,0,0)); // Put your field name in here
NumberVar Add:= 1; // put the number of days here to add (a positive number)
NumberVar Added := 0;

WHILE Added < Add
Do (target := target +1;
if dayofweek (target) in 2 to 6 and not (target in holidays)
then Added:=Added+1
else Added:=Added);
Target



//Formula #3
Checking to see if the moved date falls in weekend or holiday then use formula#2 to add business day or leave it like that

whilereadingrecords;
datevar array holidays;
if dayofweek({formula1})=1 or dayofweek({formula1})=7 or (date({@formula1}) in holidays) then
{@formula2} else
({@formula1})

//Formula #4 Calculating Business Days from Ken Hamady
WhileReadingRecords;
Local DateVar Start := date({@formula3}); // place your Starting Date here
Local DateVar End := date({@formula##for end date justlike startdate}); // 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 DayOfWeek ( Holidays ) in 2 to 6 and
Holidays in start to end then Hol:=Hol+1 );

Weeks + Days - Hol


//Formula#5 Calculating Business Hours

WhileReadingRecords;
NumberVar Days := {@Business Days}; // The field that calculates your business days
TimeVar SetStart := TimeValue( "8:00"); // The start your work day
TimeVar SetEnd := TimeValue("5:00:00PM"); // The end your work day
TimeVar StartTime := TimeValue({@formula3});// The data field that holds your Start Time
TimeVar EndTime := TimeValue({@formulafor End Date Like Formula3}); // The data field that holds your End Time

//These lines are only needed if your times are strings that do not indicate AM or PM, like "3:30"
//They will convert afternoon times to PM. Of course, this won't work if your workday is over 12 hours.
//If StartTime < (SetEnd - 43200) then StartTime := StartTime + 43200;
//If EndTime < (SetEnd - 43200) then EndTime := EndTime + 43200;

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



It worked for me.

Thought it might be helpful for someone.