Calculating Business Hours - open/close outside bus. hrs

[Kmccormic]

Good Afternoon:

I’m trying to calculate the time to resolve tickets during business hours in (hh:mm) format. I can’t seem to get the formula right.

I am excluding weekends, holidays, and non-business hours. Tickets can arrive 24x7, and may be resolved outside of business hours. Business hours are Mon-Fr 8:30AM – 4:30PM. Below is some sample data, with what I calculate to be the hours, and what I get from my formulas. Ticket 43 starts and stops on a Saturday yet comes out as 8 hours. I’m using Crystal 2008. Thank you for any help you can provide.


WO# Start Stop TrueBus.Hrs Calc.Bus.Hrs
27 12/18/09 4:22:36PM 12/21/09 10:53:18AM 2.52 2.51
28 12/19/09 11:04:35AM 12/21/09 10:53:05AM 2.38 2.39
29 12/21/09 10:53:30AM 1/5/10 10:00:28AM 71.12 71.12
30 12/23/09 4:25:32PM 1/6/10 2:11:19PM 61.77 61.76
31 1/4/10 2:22:44PM 1/6/10 2:10:07PM 15.80 15.79
32 1/5/10 2:51:32PM 1/6/10 2:10:25PM 7.32 7.32
34 1/6/10 8:57:22AM 1/6/10 2:10:49PM 5.22 5.22
35 1/6/10 9:05:31AM 1/12/10 3:51:13PM 38.77 38.76
43 1/9/10 1:22:54PM 1/9/10 1:23:42PM 0.00 8.00


Formulas
//Opendate to Local Time
UTCtolocal({TASKS.OPENDATE})


//Closedate to Local Time}
UTCtoLocal({TASKS.CLSDDATE})


//Holidays
//Holiday array of dates, goes in Header then that section suppressed.
BeforeReadingRecords;
DateVar Array Holidays := [
Date(2009,12,25),
Date(2010,1,1)
];
0



//1 MoveClose
//Move close date to next day if it is after business hours,
//and move to business opening time if it comes before business hours.

If Time({@Closedate to Local Time}) > Time("4:30:00 PM") Then
Datetime(Date(Dateadd("d",1,{@Closedate to Local Time})), Time(08,30,00)) Else
If Time({@Closedate to Local Time}) < Time(08,30,00) Then
Datetime(Date({@Closedate to Local Time}), Time(08,30,00)) Else
{@Closedate to Local Time}


//1 MoveStart
//Move start date to next day if it is after business hours,
//and move to business opening time if it comes before business hours.

If Time({@Opendate to Local time}) > Time(16,30,00) Then
Datetime(Date(Dateadd("d",1,{@Opendate to Local time})), Time(08,30,00)) Else
If Time({@Opendate to Local time}) < Time(08,30,00) Then
Datetime(Date({@Opendate to Local time}),Time(08,30,00)) Else
{@Opendate to Local time}


//2 AddDayIfHol/Wknd-Close
//Add business days to the close date if it falls on a Holiday or Weekend day

WhileReadingRecords;
DateVar Array Holidays;
DatetimeVar Target:= Datetime(Date({@1 MoveClose}),Time(16,30,0));
NumberVar Add:= 1;
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



//2 AddDayIfHol/Wknd-Open
//Add business days to the start date if it falls on a Holiday or Weekend day

WhileReadingRecords;
DateVar Array Holidays;
DatetimeVar Target:= DateTime(Date({@1 MoveStart}),Time(8,30,0));
NumberVar Add:= 1;
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



//3 Check Adj Day –Close
// Check if the moved date falls on weekend or holiday, then uses formula
//{@2 AddDayIfHol/Wknd-Open} to add a business day or leave it as is.

WhileReadingRecords;
DateVar array Holidays;
If DayofWeek({@1 MoveClose}) = 1 or DayofWeek({@1 MoveClose}) = 7 or
(Date({@1 MoveClose}) in Holidays) then {@2 AddDayIfHol/Wknd-Close} Else
({@1 MoveClose})


//3 Check Adj Day –Open
// Check if the moved date falls on weekend or holiday, then uses formula
//{@2 AddDayIfHol/Wknd-Open} to add a business day or leave it as is.

WhileReadingRecords;
DateVar array Holidays;
If DayofWeek({@1 MoveStart}) = 1 or DayofWeek({@1 MoveStart}) = 7 or
(Date({@1 MoveStart}) IN Holidays) then {@2 AddDayIfHol/Wknd-Open} Else
({@1 MoveStart})


//4 Bus. Days between 2 dates
//Calculate Business Days between Open and Close date

WhileReadingRecords;
Local DateVar Start:=Date({@3 Check Adj Day -Open});
Local DateVar End:=Date({@3 Check Adj Day -Close});
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


// 5 Bus Hrs between 2 dates
// Calculate Number of business hours between start and stop dates
//dependent on business days between start and stop dates formula.

WhileReadingRecords;
NumberVar Days := {@4 Bus. Days between 2 dates};
TimeVar SetStart := TimeValue("8:30:00 AM");
TimeVar SetEnd := TimeValue("4:30:00 PM");
TimeVar StartTime := TimeValue({@3 Check Adj Day -Open});
TimeVar EndTime := TimeValue({@3 Check Adj Day -Close});

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


Regards,
Kara

 

[Kmccormic]

Hi Everyone:

I got it to work. I needed to correct the assigned time in this piece of code to reflect my start time of 8:30. This is a wonderful code, thank you to Raja2008 for posting it earlier!

//2 AddDayIfHol/Wknd-Close
WhileReadingRecords;
DateVar Array Holidays;
DatetimeVar Target:= Datetime(Date({@1 MoveClose}),Time(16,30,0));
//changed the 16,30,0 here to 08,30,00

 

[lbass]

I believe this code actually originated with Ken Hamady.

-LB