Time Duration Formula Problem 1

[WestView]

Hello All,

I have the following formula to calculate the time duration (in business hours) it takes to resolve help desk tickets. The formula below works well….

Local DateTimeVar StartDate := DateTime({PROBLEMS.OPEN_DATE},{PROBLEMS.OPEN_TIME}) - (5/24);
Local DateTimeVar EndDate := DateTime({PROBLEMS.SOLVED_DATE},{PROBLEMS.SOLVED_TIME}) - (5/24);
Local NumberVar TotDuration;
Local NumberVar TotWeekends;
Local NumberVar TotNonBusinessHrs;

TotNonBusinessHrs := DateDiff ("d", StartDate, EndDate) -
   DateDiff ("ww", StartDate, EndDate, crSaturday) -
   DateDiff ("ww", StartDate, EndDate, crSunday);

TotNonBusinessHrs := TotNonBusinessHrs * 14;

TotDuration :=  DateDiff ("n", StartDate, EndDate);
TotWeekends :=   (DateDiff ("ww", StartDate, EndDate, crSaturday) +
                DateDiff ("ww", StartDate, EndDate, crSunday) );

TotWeekends := TotWeekends * 24;
TotDuration := TotDuration / 60;
TotDuration := (TotDuration - TotWeekends) - TotNonBusinessHrs;

TotDuration

…with the exception of a few oddities that display negative durations. An example is below.

Open_Date		       Solved_Date		     Duration
4/6/2005  7:43:00 PM	 4/7/2005  7:36:18 AM	 -2.12
4/26/2005  7:56:40 PM	4/27/2005  8:38:51 AM	-1.30
4/5/2005  6:35:06 PM	 4/6/2005  7:53:38 AM	 -0.70
4/19/2005  5:24:18 PM	4/20/2005  6:49:47 AM	-0.58
4/14/2005  6:20:11 PM	4/15/2005  7:50:53 AM	-0.50

What’s wrong with this formula!?!?!?!

Thanks for any/all assistance!!!

- Tom

 

[lbass]

What are your business hours? It looks like you have a 10-hour day (M-F) since you are showing non-business hours as 14, and yet, if this is true, in the examples you show, at least one of the time (open or solved) must fall outside of regular business hours.

Also, how about showing some examples that work correctly with this formula? I wonder if the open and solve times on these fall within regular business hours.

-LB

 

[southbean]

Hi LB,

Thank you for your post and please forgive the lateness of my reply. It seems my old login is no longer working. Very frustrating!!!

You’re correct regarding the business hours (10 hours 8:00am -6:00pm, M-F) Here’s a sample of some correct examples from the report.

OPEN_DATE	            SOLVED_DATE	          Duration
5/4/2005   9:13:34 AM	5/4/2005  11:28:57 AM	2.25
5/13/2005  4:36:05 PM	5/16/2005  8:52:17 AM	2.27
5/22/2005  11:34:54 AM   5/23/2005  3:51:39 AM	2.28

Again, thank your for your reply and please forgive my tardy reply!!!

- Tom

 

[lbass]

You have to establish rules about how to treat opening and solving times that fall outside of business hours and then build that into the formula.

-LB

 

[southbean]

Thanks LB,

Yes, I see what you mean. I'll work on that.

However, I've just been told I have another issue regarding this formula (I did not write it, just inherited it). Another field has been added to the Duration calculation – Closed_Date.

If a problem was not able to be solved (by that group) it is closed (and sent to another group). So, a date may NOT be populated in the Solved_Date field for that record but it should have a Closed_Date. Therefore I now I think I need to add an if-else-then statement to this to use the Closed_Date if the Solved_Date Is Null.

I’ve tried to work this out, but I’m just pathetic with variables.

Any help with this problem would be greatly appreciated!!!

- Tom

 

[southbean]

Hello,

I've come up with this - to use the field that is not null - but it still does not work. Augh!

Local DateTimeVar StartDate := DateTime({PROBLEMS.OPEN_DATE},{PROBLEMS.OPEN_TIME}) - (5/24);
Local DateTimeVar SolvedDate := DateTime({PROBLEMS.SOLVED_DATE},{PROBLEMS.SOLVED_TIME}) - (5/24);
Local DateTimeVar ClosedDate := DateTime({PROBLEMS.CLOSE_DATE},{PROBLEMS.CLOSE_TIME}) - (5/24);
Local NumberVar TotDuration;
Local NumberVar TotWeekends;
Local NumberVar TotNonBusinessHrs;

TotNonBusinessHrs := 
If SolvedDate = DateTime(0,0,0) Then
DateDiff ("d", StartDate, ClosedDate) -
   DateDiff ("ww", StartDate, ClosedDate, crSaturday) -
   DateDiff ("ww", StartDate, ClosedDate, crSunday)
Else
DateDiff ("d", StartDate, SolvedDate) -
   DateDiff ("ww", StartDate, SolvedDate, crSaturday) -
   DateDiff ("ww", StartDate, SolvedDate, crSunday);

TotNonBusinessHrs := TotNonBusinessHrs * 14;

TotDuration :=  
If SolvedDate = DateTime(0,0,0) Then
DateDiff ("n", StartDate, ClosedDate)
Else
DateDiff ("n", StartDate, SolvedDate);

TotWeekends := 
If SolvedDate = DateTime(0,0,0) Then
(DateDiff ("ww", StartDate, SolvedDate, crSaturday) +
                DateDiff ("ww", StartDate, ClosedDate, crSunday))
Else
(DateDiff ("ww", StartDate, ClosedDate, crSaturday) +
                DateDiff ("ww", StartDate, SolvedDate, crSunday));

TotWeekends := TotWeekends * 24;
TotDuration := TotDuration / 60;
TotDuration := (TotDuration - TotWeekends) - TotNonBusinessHrs;

TotDuration

 

[jdemmi]

I am trying to do something similar to the first formula you included in this thread.

I am see where you are identifying the # of business hours per day. But where are you, or can you specify the actual START business hour and END business hour.

In your case, 8:00 AM (start) and 6:00 PM (end)

-- Jason
"It's Just Ones and Zeros

 

[GJParker]

Take a look at this thread

thread767-891598

HTH

Gary Parker
MIS Data Analyst
Manchester, England