Calculating time on an open ticket for business hours

[ccanalia]

I have this report I am working on in CR10 with a SQL database from our support site. I am trying to calculate the total business hours each ticket is open. My formulas work for anything that was submitted during business hours, anything submitted after or before business hours is showing an incorrect negative number because it is treating the date as the date submitted, rather than same day if submitted before business hours, or next day if submitted after business hours.

My formula for business days is this.....

WhileReadingRecords;
Local DateVar Start := CDate ({Cases.CreateDate});
Local DateVar End := CDate ({@Close Date});
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


My formula for hours open is this....

WhileReadingRecords;
NumberVar Days := {@Business Days Formula};
TimeVar SetStart := TimeValue( "9:00");
TimeVar SetEnd := TimeValue("18:00");
TimeVar StartTime := TimeValue({Cases.CreateDate});
TimeVar EndTime := TimeValue({@Close Date});


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)

And my holiday formula is this....

BeforeReadingRecords;
DateVar Array Holidays := [
Date (2004,11,25),
Date (2004,11,26),
Date (2004,12,25),
Date (2005,01,03)
];
0


What do I need to add in order to get CR to put the off hours in the right category?

Thanks,

Chris