Issues with Business Hours Calculation

[elweed004]

thread767-1531876

I have been trying to get a report to show business hours vs customer hours for Remedy Tickets. I have used the formulas in the referenced thread and it is working with one exception. When the ticket is created on a Friday, I am getting a negative number back on the Business Hours calculation.

We are 24/7 operations for some areas to include our Service Desk, and we also have on-call personnel, so there are times that the tickets will be opened or closed outside of business hours. I have taken what raja2008 did on the referenced thread, but I have had to do two formulas (one for start time and one for end time) for each of raja2008's formulas 1, 2 and 3.

Again, the only time that I am having any issue is when the ticket is created on a Friday and I am not sure why that is. Any help would be greatly appreciated. Please forgive me if I have missed anything as this is my first post on this site.

Here is what I have done:


//Formula 1.1 (@_MoveStartDate)
if time({HPD_Help_Desk.Status_History_New_TIME}) > time("5:00:00PM") then
datetime(date(dateadd("d",1,{HPD_Help_Desk.Status_History_New_TIME})),time(6,0,0)) else
if time({HPD_Help_Desk.Status_History_New_TIME}) < time(6,0,0) then
datetime(date({HPD_Help_Desk.Status_History_New_TIME}),time(6,0,0)) else
{HPD_Help_Desk.Status_History_New_TIME}


//Formula 1.2 (@_MoveEndDate)
if time({HPD_Help_Desk.Last_Resolved_Date}) > time("5:00:00PM") then
datetime(date(dateadd("d",1, {HPD_Help_Desk.Last_Resolved_Date})),time(6,0,0)) else
if time({HPD_Help_Desk.Last_Resolved_Date}) < time(6,0,0) then
datetime(date({HPD_Help_Desk.Last_Resolved_Date}),time(6,0,0)) else
{HPD_Help_Desk.Last_Resolved_Date}


//Formula 2.1 (@_AddStartDay)
WhilereadingRecords;
DateVar Array Holidays;
DatetimeVar Target:=datetime(date({@_MoveStartDate}),time(6,0,0)); // Put your field name in here
NumberVar Add:= 1; // put the number of days here to add (a positive number)
NumberVar Added := 0;

WHILE Added < Add
Do (target := target +1;
if dayofweek (target) in 2 to 6 and not (target in holidays)
then Added:=Added+1
else Added:=Added);
Target


//Formula 2.2 (@_AddEndDay)
WhilereadingRecords;
DateVar Array Holidays;
DatetimeVar Target:=datetime(date({@_MoveEndDate}),time(6,0,0)); // Put your field name in here
NumberVar Add:= 1; // put the number of days here to add (a positive number)
NumberVar Added := 0;

WHILE Added < Add
Do (target := target +1;
if dayofweek (target) in 2 to 6 and not (target in holidays)
then Added:=Added+1
else Added:=Added);
Target


//Formula 3.1 (@_AddStartDay2)
whilereadingrecords;
datevar array holidays;
if dayofweek({@_MoveStartDate})=1 or dayofweek({@_MoveStartDate})=7 or (date({@_MoveStartDate}) in holidays) then
{@_AddStartDay} else ({@_MoveStartDate})


//Formula 3.2 (@_AddEndDay2)
whilereadingrecords;
datevar array holidays;
if dayofweek({@_MoveEndDate})=1 or dayofweek({@_MoveEndDate})=7 or (date({@_MoveEndDate}) in holidays) then
{@_AddEndDay} else ({@_MoveEndDate})


//Formula 4 (@_WorkDays)
WhileReadingRecords;
Local DateTimeVar Start := {@_AddStartDay2}; // place your Starting Date here
Local DateTimeVar End := {@_AddEndDay2}; // 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


//Formula 5 (@_WorkHours)
WhileReadingRecords;
NumberVar Days := {@_WorkDays}; // The field that calculates your business days
TimeVar SetStart := TimeValue("6:00:00AM"); // The start your work day
TimeVar SetEnd := TimeValue("5:00:00PM"); // The end your work day
TimeVar StartTime := TimeValue({@_AddStartDay2});// The data field that holds your Start Time
TimeVar EndTime := TimeValue({@_AddEndDay2}); // 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)


//Formula 6 (@_Holidays)
BeforeReadingRecords;
DateVar Array Holidays := [
Date (2011,01,01),
Date (2011,01,17),
Date (2011,02,21),
Date (2011,05,30),
Date (2011,06,04),
Date (2011,09,05),
Date (2011,10,10),
Date (2011,11,11),
Date (2011,11,24),
Date (2011,12,26),
Date (2012,01,02),
Date (2012,01,16),
Date (2012,02,20),
Date (2012,05,28),
Date (2012,06,04),
Date (2012,09,03),
Date (2012,10,08),
Date (2012,11,12),
Date (2012,11,22),
Date (2012,12,25),
Date (2013,01,01),
Date (2013,01,21),
Date (2013,02,18),
Date (2013,05,27),
Date (2013,07,04),
Date (2013,09,02),
Date (2013,10,14),
Date (2013,11,11),
Date (2013,11,28),
Date (2013,12,25),
Date (2014,01,01),
Date (2014,01,20),
Date (2014,02,17),
Date (2014,05,26),
Date (2014,07,04),
Date (2014,09,01),
Date (2014,10,13),
Date (2014,11,11),
Date (2014,11,27),
Date (2014,12,25)];
0