How do I convert time from text to show business hours and calculate averages

[LMGroup]

I'm using CR 2008 with an ODBC connection to a db on SQL Server 2005. The following code does almost everything that I need. I also need to be able to calculate the average response time. I'm not sure what to use or how to convert it to what I need. My times are based on a 9 hour business day. Weekends, holidays, and non-business hours are removed. The ToText result that is displayed shows the number of days, but these are 9 hour days, not 24. CR won't let me summarize the field. Any help would be greatly appreciated.

whileprintingrecords;

datetimevar createdate:={Cases.Case_CreatedDate};
numbervar createyear:=datepart("yyyy", {Cases.Case_CreatedDate});
numbervar createmonth:=datepart("m", {Cases.Case_CreatedDate});
numbervar createday:=datepart("d", {Cases.Case_CreatedDate});
numbervar createdayofweek:=dayofweek({Cases.Case_CreatedDate});
datevar createdateonly;

datetimevar respondate:={CaseProgress.Case_TimeStamp};
numbervar respondyear:=datepart("yyyy", {CaseProgress.Case_TimeStamp});
numbervar respondmonth:=datepart("m", {CaseProgress.Case_TimeStamp});
numbervar responday:=datepart("d", {CaseProgress.Case_TimeStamp});
numbervar respondayofweek:=dayofweek({CaseProgress.Case_TimeStamp});
datevar respondateonly;

numbervar secadjust:=0; //if interval spans a weekend(s) or evenings then time is removed
numbervar daydiff:=0;  //number of days between create and respond
numbervar respondtime;
numbervar Days; 
numbervar Hours; 
numbervar Minutes; 
numbervar Seconds;
shared numbervar respondflag;  //reset at the case group header level
numbervar i:=1;    //for looping through the Holidays
numbervar Hol:=0;
DateVar Array Holidays;

If {CaseProgress.Case_Stage}="Responded" and respondflag=0 then
   (

    if createdayofweek = 7 then     //if Saturday, make Monday 8am
        (createdate:=dateadd("d",2,createdate);
        createyear:=datepart("yyyy", createdate);
        createmonth:=datepart("m", createdate);
        createday:=datepart("d", createdate);
        createdate := cdatetime(createyear,createmonth,createday,8,00,00);)
    else if createdayofweek = 1 then     //if Sunday, make Monday 8am
        (createdate:=dateadd("d",1,createdate);
        createyear:=datepart("yyyy", createdate);
        createmonth:=datepart("m", createdate);
        createday:=datepart("d", createdate);
        createdate := cdatetime(createyear,createmonth,createday,8,00,00);)
    else if createdayofweek = 6 and time(createdate) > time(16,59,59) then  //if Friday after 5pm, make Monday 8am
        (createdate:=dateadd("d",3,createdate);
        createyear:=datepart("yyyy", createdate);
        createmonth:=datepart("m", createdate);
        createday:=datepart("d", createdate);
        createdate := cdatetime(createyear,createmonth,createday,8,00,00);)
    else if time(createdate) > time(16,59,59) then //if after 5pm, make next day 8am
        (createdate:=dateadd("d",1,createdate);
        createyear:=datepart("yyyy", createdate);
        createmonth:=datepart("m", createdate);
        createday:=datepart("d", createdate);
        createdate := cdatetime(createyear,createmonth,createday,8,00,00);)
    else if time(createdate) < time(8,00,00) then  //if before 8am, make 8am
        createdate := cdatetime(createyear,createmonth,createday,8,00,00);

    createdateonly := Date(createdate);

    if respondayofweek = 7 then     //if Saturday, make Monday 8am
        (respondate:=dateadd("d",2,respondate);
        respondyear:=datepart("yyyy", respondate);
        respondmonth:=datepart("m", respondate);
        responday:=datepart("d", respondate);
        respondate := cdatetime(respondyear,respondmonth,responday,8,00,01);)
    else if respondayofweek = 1 then     //if Sunday, make Monday 8am
        (respondate:=dateadd("d",1,respondate);
        respondyear:=datepart("yyyy", respondate);
        respondmonth:=datepart("m", respondate);
        responday:=datepart("d", respondate);
        respondate := cdatetime(respondyear,respondmonth,responday,8,00,01);)
    else if respondayofweek = 6 and time(respondate) > time(16,59,59) then  //if Friday after 5pm, make Monday 8am
        (respondate:=dateadd("d",3,respondate);
        respondyear:=datepart("yyyy", respondate);
        respondmonth:=datepart("m", respondate);
        responday:=datepart("d", respondate);       
        respondate := cdatetime(respondyear,respondmonth,responday,8,00,01);)
    else if time(respondate) > time(16,59,59) then //if after 5pm, make next day 8am
        (respondate:=dateadd("d",1,respondate);
        respondyear:=datepart("yyyy", respondate);
        respondmonth:=datepart("m", respondate);
        responday:=datepart("d", respondate);
        respondate := cdatetime(respondyear,respondmonth,responday,8,00,01);)
    else if time(respondate) < time(8,00,00) then  //if before 8am, make 8am
        respondate := cdatetime(respondyear,respondmonth,responday,8,00,01);

    respondateonly := Date(respondate);

    For i := 1 to Count(Holidays) Do
        (
        if createdateonly = Holidays[i] then
            createdate:=dateadd("d",1,createdate);
        if respondateonly = Holidays[i] then
            respondate:=dateadd("d",1,respondate);
        if Holidays[i] > createdateonly and Holidays[i] < respondateonly then
            Hol := Hol +1;
        );

    createdateonly := Date(createdate);
    respondateonly := Date(respondate);

    daydiff := DateDiff("d", createdate, respondate);

    if daydiff >= 1 then  //remove the number of seconds of nonbusiness hour time
        (if DateDiff("ww",createdate, respondate) <> 0 then  
            (daydiff := daydiff - (3*DateDiff("ww",createdate, respondate)); //remove 3 days for every weekend
            secadjust := 226800 * DateDiff("ww",createdate, respondate)); //add weekend seconds (63 hrs)
        secadjust := secadjust + (54000 * daydiff) + (32400 * Hol)); //add non-business hr seconds (15 hrs) and holiday seconds (9 hrs)

    respondtime:=DateDiff("s", createdate, respondate)-secadjust;
    respondflag:=1;

    Days    := Truncate  (respondtime / 32400);     //9 hr business day
    Hours   := Truncate  (Remainder ( respondtime , 32400) / 3600) ; 
    Minutes := Truncate  (Remainder ( respondtime , 3600) / 60) ; 
    Seconds := Remainder (respondtime , 60) ;

    Totext ( Days ,    '##' ) +  ':' + 
    Totext ( Hours ,   '00' ) +  ':' + 
    Totext ( Minutes , '00' ) +  ':' + 
    Totext ( Seconds , '00' ) 
    )

 

[SkipVought]

hi,

Please post an example of the date/time values that you have to work with and also post the expected result for each example.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[LMGroup]

The code only calculates the time for the first record of each case. What I need is an average of all the times in the last column. The numbers are calculating correctly. 8/31 & 9/01 were weekend days and 9/02 is a holiday. Business hours are 8am-5pm, M-F.

	     Reference Id	        Created	                Responded	      # Business Days:Hrs:Min:Sec

	120 54295	8/30/2013   2:30:00PM	9/3/2013  10:08:57AM	 0:04:38:57
	120 54295	8/30/2013   2:30:00PM	9/6/2013   9:56:57AM

	120 54302	8/30/2013   4:03:00PM	9/4/2013   4:03:04PM	 2:00:00:04
	120 54302	8/30/2013   4:03:00PM	9/4/2013   3:54:25PM

	1 54278	        8/30/2013  10:11:00AM	9/3/2013  10:08:32AM	 0:08:57:32
	1 54278         8/30/2013  10:11:00AM	9/3/2013  12:32:51PM

	1 54279	        8/30/2013  10:16:00AM	8/30/2013   2:26:52PM	 0:04:10:52

	1 54280	        8/30/2013  10:16:00AM	9/10/2013   4:00:23PM	 6:05:44:23

	1 54283	        8/30/2013  10:46:00AM	9/3/2013   3:16:49PM	 1:04:30:49
	1 54283	        8/30/2013  10:46:00AM	9/3/2013   3:52:02PM

	1 54284	        8/30/2013  11:31:00AM	8/30/2013   1:17:00PM	 0:01:46:00
	1 54284	        8/30/2013  11:31:00AM	9/3/2013   4:00:22PM

	1 54288	        8/30/2013  11:56:00AM	8/30/2013  12:57:32PM	 0:01:01:32
	1 54288	        8/30/2013  11:56:00AM	8/30/2013   1:29:21PM

	1 54290	        8/30/2013  12:46:00PM	9/3/2013   3:15:30PM	 1:02:29:30
	1 54290	        8/30/2013  12:46:00PM	9/3/2013   3:15:38PM
	1 54290	        8/30/2013  12:46:00PM	9/4/2013  12:11:04PM

	1 54292	        8/30/2013   2:22:00PM	9/3/2013   9:59:50AM	 0:04:37:50

	1 54295	        8/30/2013   2:42:00PM	9/4/2013   5:21:55PM	 2:02:18:01
	1 54295	        8/30/2013   2:42:00PM	8/30/2013   3:08:58PM
	1 54295	        8/30/2013   2:42:00PM	8/30/2013   4:04:49PM

	1 54298	        8/30/2013   3:07:00PM	9/3/2013  10:00:10AM	 0:03:53:10

	1 54303	        8/30/2013   4:22:00PM	9/4/2013   8:44:35AM	 1:01:22:35

	1 54305	        8/30/2013  11:04:00PM	9/6/2013   2:54:38PM	 3:06:54:38

	1 54313	        8/31/2013  11:49:00PM	9/3/2013   9:34:32AM	 0:01:34:32
	1 54313	        8/31/2013  11:49:00PM	9/3/2013   2:19:20PM

	1 54325	        9/2/2013   6:49:00PM	9/4/2013  10:04:21AM	 1:02:04:21

 

[SkipVought]

The rub, IMNSHO, is to determine the a workday. My company has a manufacturing calendar, so it makes it a smap to determine whether a date is a workday.

But here's some simple code for calculating business hours based on workdays. You need actual date/time values, NOT strings! This code worked on your first example row, taking into account Labor Day.

Adapt to CR, as this is VBA.
Use Start & End date/time values.
i is a loop counter that goes from the STart date ot the ENd date
stFR & enFR are the FRACTIONAL values of the Date/Time value representing TIME.
The Constants are to identify the start and end times of the day and the hours in a full working day.
BTW, all these values are normalized to DAYS, the units of Date/Time values.

Function BusinessHours(ST, EN) As Single
    Dim i As Long, stFR As Single, enFR As Single

    Const DayST = 8 / 24
    Const DayEN = 17 / 24
    Const DayALL = 9 / 24

    stFR = ST - Int(ST)
    enFR = EN - Int(EN)

    For i = Int(ST) To Int(EN)
        If i = WorkDay Then
            If i = Int(ST) And stFR >= DayST And stFR < DayEN Then
'[b]this calculates the hours at the BEGINNING of the period  [/b]   
                BusinessHours = BusinessHours + DayEN - stFR
            
            ElseIf i = Int(EN) And enFR >= DayST And enFR < DayEN Then
'[b]this calculates the hours at the END of the period  [/b]   
                BusinessHours = BusinessHours + enFR - DayST
            
            Else
'[b]this calculates the hours DURING of the period  [/b]   
                BusinessHours = BusinessHours + DayALL
            
            End If
        
        End If
    Next
End Function

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]