Timestamp prior to Business Hours, but need to know business hours

[CLKCLK]

I am working on a report that need to calculate the number of business hours between two dates. The problem that I am running into is the first date is the datestamp of the email or voicemail so that can come in at any time of the day. But the SLA doesn't begin until our business hours start. Our business hours are 06:00 to 18:00. Here is an example of 2 dates from the database:

example 1
table.notification.date.time = 2/11/2010 6:36:00 PM
table.open.date.time = 2/12/2010 6:13:48 AM
# of business hours are 00:13:48.

example 2
table.notification.date.time = 2/11/2010 10:00:00 PM
table.open.date.time = 2/12/2010 6:54:44 AM
# of business hours are 0:54:44.


When I use the following business days and work hours formula for the example 1 I get a time of -0.37 for @WorkHours

@BusinessDays
//The number of "Business Days Formula" between two dates
WhileReadingRecords;
Local DateVar Start := Date({table.Notification.Date.Time}); // place your Starting Date here
Local DateVar End := Date({table.Open.Date.Time}); // 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

@Holidays
//Holiday Listing formula to go into the report header of the report.
BeforeReadingRecords;
DateVar Array Holidays := [
Date (2009,11,26),
Date (2009,12,25),
Date (2010,01,01),
Date (2010,02,15),
Date (2010,05,31),
Date (2010,07,05),
Date (2010,09,06),
Date (2010,10,11),
Date (2010,11,11),
Date (2010,11,25),
Date (2010,12,24)
];
0

@WorkHours
//The number of "Work Hours" between two DateTime Values
WhileReadingRecords;
NumberVar Days := {@BusinessDays}; // The field that calculates your business days
TimeVar SetStart := TimeValue( "6:00"); // The start your work day
TimeVar SetEnd := TimeValue("18:00"); // The end your work day
TimeVar StartTime := TimeValue({table.Notification.Date.Time});// The data field that holds your Start Time
TimeVar EndTime := TimeValue({table.Open.Date.Time}); // The data field that holds your End Time




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)


After this I need to create a formula to convert @workDays to @seconds and then use this to display the hhmmss

whileprintingrecords;
numberVar dur := {@Seconds}; //the seconds between 2 dates
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;

hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
sec := Remainder(dur,60);

hhmmss := totext(hrs,"00") + ":" + totext(min,"00") + ":" + totext(sec,"00");

hhmmss


Is there an obvious mistake I am making here or can anyone offer any advice to make this work with a timestamp that comes in prior to business hours?

Thanks

CLK

 

[CLKCLK]

I was thinking about this more and was wondering if there is a way to write a formula that would change the {table.Notification.Date.Time} to (current date 6:00:00 AM) if it is less than (current date 6:00:00 AM) so in the example 1 above the {table.Notification.Date.Time} of 2/11/2010 6:36:00 PM would change to 2/12/2010 6:00:00 AM.

Thoughts???

CLK

 

[Madawc]

I think you're trying to code a lot of stuff that Crystal will do for you. Check the DateDiff command, also the various automatic totals, see FAQ767-6524.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[CLKCLK]

I need business hours, where the <notificationtimedate> could be before the business hours declared in the formula. I didn't think that datediff can calculate that.