Helpdesk Call SLA Response Report 1

[Parkypark]

I am trying to write a report that shows how we have performed against a set of SLA's
We have 5 Priorities of call that I must report on.
2 of them I have done as the response clock ticks 24/7.

the other 3 are causing me a problem, as the SLA clock only counts down within working hrs of 0900 to 1730.

My problem is I cannot get my Expected time to respond to take into consideration that the clock is not running 24 hrs.

I have tried and failed to get this to work

Please can anyone help me

 

[Reebo99]

This really is a bit of a bugger. I have recently developed time sheets with the same problem. We run flex-time, so we needed to clock when someone entered the building via card reader to when they left, but only take into account flex-time between the hours of 07:00 and 19:00. The way I did it was (hey, I know it ain't pretty, nut it works) to do a loop formula, adding a minute each loop. example:

(basic syntax)
dim counter as number
dim datetimecount as DateTime

datetimecount = {DateTimeofLog}
do until Cdatetime(Cdate(datetimecount),Ctime(hour(datetimecount),minute(datetimecount),00)) = Cdatetime(currentdate,ctime(Hour(currenttime),minute(currenttime),00))
if Ctime(datetimecount) >= Ctime(07,00,00) and Ctime(datetimecount) <= Ctime(19,00,00) then
counter = counter +1
end if
datetimecount = dateadd(&quot;n&quot;,1,datetimecount)
loop

formula = counter

!!Warning!! this can take a very long time depending on how many records you have, how long it has to count etc. I'm sure there is a better way to do it, but until someone tells me, I won't know.

Hope it helps, let me know. Reebo
Scotland (Raining)

 

[Naith]

Hey Reebo'n'ParkyPark,

Ken has a formula on his website (

http://www.kenhamady.com/formula.html)

that should help you out. Goes like this:

WhilePrintingRecords; 
NumberVar Days := {@Business Days Formula};  // The field that calculates your business days 
TimeVar SetStart := TimeValue( "9:00");      // The start of your work day 
TimeVar SetEnd   := TimeValue("17:30");      // The end of your work day 
TimeVar StartTime := TimeValue({Start.Time});// The data field that holds your Start Time 
TimeVar EndTime   := TimeValue({End.Time});  // 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)

This works in tandem with a business days formula (also on his website). Hope it helps you out.

Have a great weekend,

Naith

 

[BlurredVision]

Here's what I use - It excludes holidays and formats the time in more of a user friendly format then Kens.
//CALCULATE THE NUMBER OF BUSINESS HOURS
//BETWEEN FirstDateTime AND LastDateTime
//EXCLUDING HOLIDAYS IN THE TOTAL

numbervar TotalTime;

numbervar hr;
numbervar mn;
numbervar sc;

//!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!//INPUT REQUIRED _ INPUT REQUIRED _ INPUT REQUIRED

//Replace datetime(2001,12,23,11,50,0) with your starting //date
DatetimeVar FirstDateTime:= (yourdate);
//Replace datetime(2001,12,26,10,0,0) with your ending //date
DatetimeVar LastDateTime:= (yourdate);
//Replace Time(09,00,00) with your business starting time
TimeVar BusinessStartTime:= Time(08,30,00);
//Replace Time(17,00,00) with your business ending time
TimeVar BusinessEndTime:= Time(17,30,00);

//Holidays need to be assigned to all the holidays that //need to be removed from the total


DateVar NewYearsDay2002:= Date(2002,01,01);
DateVar MartinLutherKing2002:= Date(2002,01,21);
DateVar PresidentsDay2002:= Date(2002,02,18);
DateVar MemorialDay2002:= Date(2002,05,27);
DateVar IndependenceDay2002:= Date(2002,07,04);
DateVar LaborDay2002:= Date(2002,09,02);
DateVar ColumbusDay2002:= Date(2002,10,14);
DateVar VeteransDay2002:= Date(2002,11,11);
DateVar ThanksgivingDay2002:= Date(2002,11,28);
DateVar ChristmasDay2002:= Date(2002,12,25);


DateVar NewYearsDay2003:= Date(2003,01,01);
DateVar MartinLutherKing2003:= Date(2003,01,20);
DateVar PresidentsDay2003:= Date(2003,02,17);
DateVar MemorialDay2003:= Date(2003,05,26);
DateVar IndependenceDay2003:= Date(2003,07,04);
DateVar LaborDay2003:= Date(2003,09,01);
DateVar ColumbusDay2003:= Date(2003,10,13);
DateVar VeteransDay2003:= Date(2003,11,11);
DateVar ThanksgivingDay2003:= Date(2003,11,27);
DateVar ChristmasDay2003:= Date(2003,12,25);





//!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
//END INPUT REQUIRED _ END INPUT REQUIRED _ END INPUT

//Other variables used in the formula
Numbervar HoursInADay:= (BusinessEndTime - BusinessStartTime)/3600;
Numbervar Days;
Numbervar Weekends;
Numbervar Finaldays;
DateVar StartDate;
DateVar EndDate;
NumberVar halfdays;
NumberVar fulldays;
NumberVar hours;
NumberVar Holidays:=0;
timevar TrueStartTime;
timevar TrueEndTime;

//BEGIN FORMULA:
//*********************************************************

//FINISH FORMULA IF FirstDateTime OR LastDateTime IS NULL
//*********************************************************
IF FirstDateTime <=Date(0,0,0) or LastDateTime <=Date(0,0,0) then hours:= 0

//ELSE ASSIGN HOURS
//*********************************************************
ELSE
(
//ASSIGN FirstDateTime and LastDateTime
//*********************************************************
//Determine whether FirstDateTime falls within
//Start Time to End Time
if time(FirstDateTime) in BusinessStartTime to BusinessEndTime then
FirstDateTime:= FirstDateTime
else if time(FirstDateTime) > BusinessEndTime then
FirstDateTime:= datetime(date(FirstDateTime)+1, BusinessStartTime)
else if time(FirstDateTime) < BusinessStartTime then
FirstDateTime:= datetime(date(FirstDateTime), BusinessStartTime);

//Determine whether LastDateTime falls within Start Time to End //Time
if time(LastDateTime) in BusinessStartTime to BusinessEndTime then
LastDateTime:= LastDateTime
else if time(LastDateTime) > BusinessEndTime then
LastDateTime:= datetime(date(LastDateTime), BusinessEndTime)
else if time(LastDateTime) < BusinessStartTime then
LastDateTime:= datetime(date(LastDateTime)-1, BusinessEndTime);

//ASSIGN STARTDATE and ENDDATE
//*********************************************************

//if the first day falls on a weekend, StartDate is equal //to the following Monday for calculation reasons
If DayOfWeek(FirstDateTime) = 7 Then
StartDate := date(FirstDateTime) + 2
Else If DayOfWeek(FirstDateTime) = 1 Then
StartDate := date(FirstDateTime) + 1
Else StartDate:=date(FirstDateTime);

//if the last day falls on a weekend, EndDate is equal to //the following Monday for calculation reasons
If DayOfWeek(LastDateTime) = 7 Then
EndDate := date(LastDateTime) + 2
Else If DayOfWeek(LastDateTime) = 1 Then
EndDate := date(LastDateTime) + 1
Else EndDate := date(LastDateTime);

//CALCULATE DAYS AND WEEKENDS
//*********************************************************
//Calculate Days (including First day and Last day)
Days:= (EndDate - StartDate)+1;

//Calculate weekends
if Days >= 7 then
WeekEnds := (Round((Days/7),0))*2
else if DayOfWeek(StartDate) > DayOfWeek(EndDate) then
WeekEnds := 2
else WeekEnds := 0;

//CALCULATE HOLIDAYS
//*********************************************************
//---- NEW YEARS DAY ----

//2002
If dayofweek(NewYearsDay2002) <> 7 and dayofweek(NewYearsDay2002) <> 1 then
(
If NewYearsDay2002 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2003
If dayofweek(NewYearsDay2003) <> 7 and dayofweek(NewYearsDay2003) <> 1 then
(
If NewYearsDay2003 in StartDate to EndDate then
Holidays:=Holidays + 1;
);

//---- MARTIN LUTHER KING DAY ----

//2002
If dayofweek(MartinLutherKing2002) <> 7 and dayofweek(MartinLutherKing2002) <> 1 then
(
If MartinLutherKing2002 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2003
If dayofweek(MartinLutherKing2003) <> 7 and dayofweek(MartinLutherKing2003) <> 1 then
(
If MartinLutherKing2003 in StartDate to EndDate then
Holidays:=Holidays + 1;
);



//---- PRESIDENTS DAY ----


//2002
If dayofweek(PresidentsDay2002) <> 7 and dayofweek(PresidentsDay2002) <> 1 then
(
If PresidentsDay2002 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2003
If dayofweek(PresidentsDay2003) <> 7 and dayofweek(PresidentsDay2003) <> 1 then
(
If PresidentsDay2003 in StartDate to EndDate then
Holidays:=Holidays + 1;
);

//---- MEMORIAL DAY ----


//2002
If dayofweek(MemorialDay2002) <> 7 and dayofweek(MemorialDay2002) <> 1 then
(
If MemorialDay2002 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2003
If dayofweek(MemorialDay2003) <> 7 and dayofweek(MemorialDay2003) <> 1 then
(
If MemorialDay2003 in StartDate to EndDate then
Holidays:=Holidays + 1;
);

//---- INDEPENDENCE DAY ----


//2002
If dayofweek(IndependenceDay2002) <> 7 and dayofweek(IndependenceDay2002) <> 1 then
(
If IndependenceDay2002 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2003
If dayofweek(IndependenceDay2003) <> 7 and dayofweek(IndependenceDay2003) <> 1 then
(
If IndependenceDay2003 in StartDate to EndDate then
Holidays:=Holidays + 1;
);

//---- LABOR DAY ----


//2002
If dayofweek(LaborDay2002) <> 7 and dayofweek(LaborDay2002) <> 1 then
(
If LaborDay2002 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2003
If dayofweek(LaborDay2003) <> 7 and dayofweek(LaborDay2003) <> 1 then
(
If LaborDay2003 in StartDate to EndDate then
Holidays:=Holidays + 1;
);


//---- COLUMBUS DAY ----


//2002
If dayofweek(ColumbusDay2002) <> 7 and dayofweek(ColumbusDay2002) <> 1 then
(
If ColumbusDay2002 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2003
If dayofweek(ColumbusDay2003) <> 7 and dayofweek(ColumbusDay2003) <> 1 then
(
If ColumbusDay2003 in StartDate to EndDate then
Holidays:=Holidays + 1;
);


//---- VETERANS DAY ----


//2002
If dayofweek(VeteransDay2002) <> 7 and dayofweek(VeteransDay2002) <> 1 then
(
If VeteransDay2002 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2003
If dayofweek(VeteransDay2003) <> 7 and dayofweek(VeteransDay2003) <> 1 then
(
If VeteransDay2003 in StartDate to EndDate then
Holidays:=Holidays + 1;
);

//---- THANKSGIVING ----


//2002
If dayofweek(ThanksgivingDay2002) <> 7 and dayofweek(ThanksgivingDay2002) <> 1 then
(
If ThanksgivingDay2002 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2003
If dayofweek(ThanksgivingDay2003) <> 7 and dayofweek(ThanksgivingDay2003) <> 1 then
(
If ThanksgivingDay2003 in StartDate to EndDate then
Holidays:=Holidays + 1;
);


//---- CHRISTMAS ----

//2002
If dayofweek(ChristmasDay2002) <> 7 and dayofweek(ChristmasDay2002) <> 1 then
(
If ChristmasDay2002 in StartDate to EndDate then
Holidays:=Holidays + 1;
);
//2003
If dayofweek(ChristmasDay2003) <> 7 and dayofweek(ChristmasDay2003) <> 1 then
(
If ChristmasDay2003 in StartDate to EndDate then
Holidays:=Holidays + 1;
);


//CALCULATE FINALDAYS
//*********************************************************
//If the Last Day is on a weekend then FinalDays subtract //the weekend days
If DayOfWeek(LastDateTime) = 7 then
FinalDays:= FinalDays - 1;
If DayOfWeek(LastDateTime) = 1 then
FinalDays:= FinalDays - 2;

//Assign FinalDays to Days minus Holidays and Weekends
FinalDays:= Days - Holidays - WeekEnds;

//CALCULATE HOURS
//*********************************************************
//Calculate FirstDateTime and LastDateTime if falling on a weekend
//if the first day falls on a weekend, StartDate is equal //to the following Monday for calculation reasons
If DayOfWeek(FirstDateTime) = 7 Then
FirstDateTime := datetime(date(FirstDateTime) + 2, BusinessStartTime)
Else If DayOfWeek(FirstDateTime) = 1 Then
FirstDateTime := datetime(date(FirstDateTime) + 1, BusinessStartTime);

//if the last day falls on a weekend, EndDate is equal to the following Monday for calculation reasons
If DayOfWeek(LastDateTime) = 7 Then
LastDateTime := datetime(date(LastDateTime) + 2,BusinessStartTime)
Else If DayOfWeek(LastDateTime) = 1 Then
LastDateTime := datetime(date(LastDateTime) + 1, BusinessStartTime);

//If less than 24 hours involved
If FinalDays <= 1 then
(
//If first day is the same day as last day
if date(FirstDateTime) = date(LastDateTime) then
(
//If First Day starts before business start time, //assign TrueStartTime to business starttime
if time(FirstDateTime) >= BusinessStartTime then
TrueStartTime:= time(FirstDateTime)
else TrueStartTime:= BusinessStartTime;

//If Last Day ends after business end time, assign //TrueEndTime to business endtime
if time(LastDateTime) <= BusinessEndTime then
TrueEndTime:= time(LastDateTime)
else TrueEndTime:= BusinessEndTime
)
//If first day is not the same day as last day
else TrueStarttime:= BusinessStartTime;

if time(LastDateTime) <= BusinessEndTime then TrueEndTime:= time(LastDateTime)
else TrueEndTime:= BusinessEndTime;

//Assign hours to the endtime - starttime divided by 3600 (seconds in an hour)
hours:= (TrueEndTime-TrueStartTime)/3600;
)

//Else hours = how many hours on the two half days + how //many hours for the full days
Else
(
halfdays:= ((BusinessEndTime - time(FirstDateTime)) /3600 + (time(LastDateTime) - BusinessStartTime)
/3600);
fulldays:= (FinalDays-2) * HoursInADay;
hours:= halfdays + fulldays;
);
);

//DISPLAY NUMBER OF BUSINESS HOURS IN THE RANGE
//*********************************************************
TotalTime := hours * 3600;

hr := Truncate(TotalTime/3600) ;
mn := Truncate( Remainder(TotalTime/60,60) );
sc := Remainder(TotalTime,60) ;

totext(hr,&quot;00&quot+&quot;:&quot;+totext(mn,&quot;00&quot; Brian

http://www.crystalcommunity.com