Number of business days/hours -with a twist 1

[CalgaryCR9]

This is in CR9.

{@Open Time} is the following (time service call was placed):
if {SERVICE_CALL_EVENT.SVC_CALL_EVENT_TYPE}="OPEN"
or {SERVICE_CALL_EVENT.SVC_CALL_EVENT_TYPE}="OPEN_WEB"
then {SERVICE_CALL_EVENT.ACTION_DT}

{@close} is the following (time service call was closed):
if {SERVICE_CALL_EVENT.SVC_CALL_EVENT_TYPE}="CLOSE"
then {SERVICE_CALL_EVENT.ACTION_DT}

Using Ken Hamady's formulas from the following URLs:

http://www.kenhamady.com/form01.html

http://www.kenhamady.com/form13.html

I have the following two formulas:
{@business_days}
//Main formula
WhileReadingRecords;
if {SERVICE_CALL.SERVICE_LEVEL_ID}=110
then
Local DateTimeVar Start := ({@Open Time}); // place your Starting Date here
Local DateTimeVar End := {@close}; // 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 Holidays in start to end then Hol:=Hol+1 );

Weeks + Days - Hol

formula {@business_hours}
WhileReadingRecords;
if {SERVICE_CALL.SERVICE_LEVEL_ID}=110
then
NumberVar Days := {@business_days}; // The field that calculates your business days
TimeVar SetStart := TimeValue( "8:00"); // The start your work day
TimeVar SetEnd := TimeValue("17:00"); // The end your work day
TimeVar StartTime := TimeValue({@Open Time});// The data field that holds your Start Time
TimeVar EndTime := TimeValue({@close}); // The data field that holds your End Time

//These lines are only needed if your times are strings that do not indicate AM or PM, like "3:30"
//They will convert afternoon times to PM. Of course, this won't work if your workday is over 12 hours.
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)

@business_hours and @business_days are placed in the details section of my report and I get the correct output in two separate fields eg: one field stating 7 (days) and another stating 57 (minutes).

My goal is to get output in the following format DD:HH:MM:SS (eg: 07:00:57:00), instead of two separate fields. I don't know how to achieve this.

Oh, my {@holidays} formula is placed in the report header and looks like this:
//Main formula


//Holiday Listing formula to go into the report header of the report.
BeforeReadingRecords;
DateVar Array Holidays := [
Date (2006,01,01),
Date (2006,01,02),
Date (2006,04,14),
Date (2006,05,22),
Date (2006,07,03),
Date (2006,07,01),
Date (2006,08,07),
Date (2006,09,04),
Date (2006,10,09),
Date (2006,11,10),
Date (2006,12,25),
Date (2006,12,26)
];
0

 

[MisterMo]

you could create anothe formula where you conevert the results to text and join them togethere

eg. replace(totext(@business_days),'.00','') + ':' + totext(@business_hours)

Mo

 

[lbass]

Please note that the business hours formula is converting the days formula into hours, NOT adding any remaining hours to the days. So I think what you really need is to convert the hours formula into the new format. SynapseVampire has an FAQ on converting to hhmmss that you could adapt to this.

-LB