Response Time between two dates....with a twist

[MisterMo]

Here is the story so far: the user can enter request at any time, however Start time and Response time are only calculated between office hours eg. 9 to 5.

response time can be a matter of minutes, hours or even days which will be converted to hours and minutes

I did some work with Ken Hamady Formula 13 combined with Formula 1 but that only seem to work with exact hours anything else returns wrong times.

Database MS-SQL2000 CR10

Sample Data

InputTime			ResponseStartTime		ResponseEndTime
2006-01-12 14:49:09.713	2006-01-12 15:34:09.713	2006-01-13 15:49:09.713
2006-01-12 14:50:11.793	2006-01-12 15:35:11.793	2006-01-13 09:50:11.793
2006-01-12 14:50:33.387	2006-01-12 16:50:33.387	2006-01-13 09:50:33.387
2006-01-12 14:51:10.417	2006-01-12 15:44:10.417	2006-01-13 11:51:10.417
2006-01-12 14:51:26.950	2006-01-12 16:11:26.950	2006-01-13 10:51:26.950
2006-01-12 13:50:08.450	2006-01-12 14:10:08.450	2006-01-13 09:50:08.450
2006-01-12 13:50:25.073	2006-01-12 14:30:25.073	2006-01-13 11:50:25.073
2006-01-12 13:50:48.167	2006-01-12 14:22:48.167	2006-01-13 10:50:48.167
2006-01-12 13:52:03.183	2006-01-12 14:10:03.183	2006-01-13 14:52:03.183
2006-01-12 11:49:36.703	2006-01-12 12:49:36.703	2006-01-12 14:49:36.703
2006-01-12 11:50:09.500	2006-01-12 11:50:09.500	2006-01-12 13:50:09.500
2006-01-12 11:54:41.157	2006-01-12 12:24:41.157	2006-01-12 13:34:41.157
2006-01-12 13:23:35.497	2006-01-12 15:23:35.497	2006-01-13 09:23:35.497
2006-01-12 13:21:28.417	2006-01-12 13:30:28.417	2006-01-12 14:03:28.417
2006-01-12 13:49:42.730	2006-01-12 14:09:42.730	2006-01-13 08:49:42.730
2006-01-12 13:20:52.620	2006-01-12 13:32:52.620	2006-01-12 15:16:52.620
2006-01-12 13:20:29.213	2006-01-12 13:52:29.213	2006-01-12 15:06:29.213

Modified Formula generosly provided by Ken

WhileReadingRecords; 
NumberVar Days;  // The field that calculates number of business days
TimeVar SetStart := TimeValue( "9:00:00");      // The start work day
TimeVar SetEnd   := TimeValue("17:00:00");      // The end work day
TimeVar StartTime := TimeValue({CV.InputTime});// The data field that holds Start Time
TimeVar EndTime   := TimeValue({CV.ResponseEndTime});  // The data field that holds End Time

if EndTime > SetEnd then
EndTime := SetEnd; 

if StartTime < SetStart then
StartTime := SetStart;

if StartTime > SetEnd then
(
StartTime := SetStart;
Days := {@NumOfBusDays} - 1;
)
else
Days := {@NumOfBusDays};

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

Last note: I have a funny feeling that i might have to convert all to minutes or even seconds (which is what I am starting to do now) before doing the calculation, but I was hoping for a quick fix.


Thanks in advance for any help






Mo

 

[MisterMo]

Sorted

I didn't have to go as far as seconds

WhilePrintingRecords; 
NumberVar Days1;  // The field that calculates your business days
NumberVar SetStart1 := hour(TimeValue( "09:00:00"))*60;      // The start your work day
NumberVar SetEnd1   := hour(TimeValue("17:00:00"))*60;      // The end your work day
NumberVar StartTime1 := (hour(TimeValue({CV.InputTime}))*60) + minute(TimeValue({CV.InputTime}));// The data field that holds your Start Time
NumberVar EndTime1   := (hour(TimeValue({CV.ResponseEndTime}))*60) + minute(TimeValue({CV.ResponseEndTime}));  // The data field that holds your End Time

if EndTime1 > SetEnd1 then
EndTime1 := SetEnd1; 

if EndTime1 < SetStart1 then
EndTime1 := SetStart1; 

if StartTime1 < SetStart1 then
StartTime1 := SetStart1;

if StartTime1 > SetEnd1 then
(
StartTime1 := SetStart1;
Days1 := {@NumOfBusDays} - 1;
)
else
Days1 := {@NumOfBusDays};


replace(totext(truncate(((Days1)   * (SetEnd1 - SetStart1)
 - (SetEnd1 - EndTime1) 
- (StartTime1 - SetStart1) )/60)),'.00','')
+ ':' +
replace(totext(remainder (((Days1)   * (SetEnd1 - SetStart1)
 - (SetEnd1 - EndTime1) 
- (StartTime1 - SetStart1) ),60)),'.00','')

Mo