Results 1 to 8 of 8

20170518, 16:07 #1
 Join Date
 Feb 2017
 Posts
 9
 Thanks
 0
 Thanked 0 Times in 0 Posts
Time Spent in Each Status Calculation
I am trying to calculate resolution times in excel based off of a data pull from HP ALM QC.
We have separate statuses for time spent with QA, Dev, and Business. I want to produce a report showing the resolution time for each item using their Defect ID, by department.
Time recording hours (24 hours a day):
Start: 10:00 PM PST Sunday
End: 5:00 PM PST Friday
Statuses:
New (Starting point)
Open  Dev
Open  QA
Open  Bus
Open  Admin
Fixed (Stops Clock)
Failed (Starts Clock against Dev)
Closed (Stops Clock)
Cancelled (Stops Clock)
These are the columns from the data pull:
ID#
TIMSESTAMP
STATUS
VERSION
LEAD
SEVERITY
CAUSE
PARENT
FAILS
COMPANY
I would also like to conditionally format the report to highlight any resolution times in red that exceed these goals:
Resolution Time SLAs:
A: 8 hours
B: 24 hours
C: 32 hours
D: 48 hours
E: 72 hours
F: 72 hours
I don't know where to start. We were using VBA for the way we used to calculate resolution time, but the error rate was over 20%.
Attached is some sample data, any help would be appreciated.Last edited by FarmerTodd; 20170518 at 16:09.

20170518, 22:42 #2
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,923
 Thanks
 152
 Thanked 747 Times in 679 Posts
Farmer,
Could you post a macro stripped .xlsx file sample?

20170519, 11:12 #3
 Join Date
 Feb 2017
 Posts
 9
 Thanks
 0
 Thanked 0 Times in 0 Posts

20170519, 15:15 #4
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,923
 Thanks
 152
 Thanked 747 Times in 679 Posts
We were using VBA for the way we used to calculate resolution time
1. I am guessing that your 4 departments Dev, Bus, Admin, and QA. Can you confirm?
2. Can you elaborate how resolution times should be calculated?
3. Can it be assumed that Defect ID = ID# (Column A)?
4. If your resolution times exceed 72 hours then it certainly exceeds 48, 32, 24, and 8 hours. How do you want to differentiate them; different colors?
5. Resolutions E and F are both 72 hours. Please explain

20170519, 16:03 #5
 Join Date
 Feb 2017
 Posts
 9
 Thanks
 0
 Thanked 0 Times in 0 Posts
Thank you. Ill explain the questions, but now this rests on figuring out just a formula, I'll explain below.
1. Correct
2. I need to total the amount of time that was spent in each status, excluding weekends (Friday 5pm  Sunday 10pm)
3. Yes
4. The color could be the same for all, but the highlighting depends on whether or not the time exceeded the goal for that severity. I don't want 8 hours highlighted for all items, just for items with severity "A"
5. They are two separate severities that will show up, both with the same resolution time.
What I have so far is a formula calculating the time
This gives me the time that the item in cell A10 spent in the status in C10 before moving to the next status (while displaying negative values as 0)
=MAX(0,B11B10)
The issue here, is that I cannot account for anything but actual total hours, but I want to remove the hours between Fridays at 5:00 pm and Sundays at 10:00 pm.
This formula calculated the workdays only, but uses full 24 hour days, not specifying the number of hours spent in each status, just showing full days.
=MAX(0,NETWORKDAYS(B10,B11))
What would also be nice, is if I could use reference cells to determine what hours are calculated. Sometimes we want just the time spent with our offshore workers, sometimes just onshore working hours. So if I could input data that specifies the working hours in reference cells, rather than changing the formulas, that would be helpful.
What I have figured out so far, is that I can use a pivot to show the data I want  I can get the totals exactly as needed, but the above formula needs to be correct for the pivot to be correct.

20170520, 11:40 #6
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,923
 Thanks
 152
 Thanked 747 Times in 679 Posts
Farmer,
I have broken down the steps into individual formulas so that you can see the progression of the entire calculation
Step1: Find the nearest Friday date at 17:00 beyond the Time Stamp Date (Column K)
In cell K2, enter the following formula then copy down
Code:=CEILING(B2,7)1 + TIME(17,0,0)
Step 2: Find the nearest Sunday date at 22:00 beyond the Time Stamp Date (Column L)
In cell L2, enter the following formula then copy down
Code:=CEILING(B2,7)+1 + TIME(22,0,0)
Step 3: Find the overlap hours from the Time Stamps and the Weekend Exclusion Period (Column M)
In cell M2, enter the following formula then copy down
Code:=MAX(0,MIN(B3,L2)MAX(B2,K2))
Step 4: Find the Resolution period (hh:mm) (Column N)
In cell N2, enter the following formula then copy down
Code:=IF(OR(C2="Closed",C3="New",B2>B3),"",B3B2M2)
I am working on the conditional formatting now
Maud

20170522, 11:48 #7
 Join Date
 Feb 2017
 Posts
 9
 Thanks
 0
 Thanked 0 Times in 0 Posts
Thank You Maud,
This is excellent. Once concern:
The overlap column is showing me only the number of hours needing to be excluded from a single weekend (53) as a max value. If the resolution time runs over 1 week, the final value is inaccurate.
Example:
M10 Should be ~318
I'm trying to find a way to multiply that number by the number of weekends between two dates, but this also appears to skew the data incorrectly. The closest I've got is:
=((B11B10)(NETWORKDAYS(B10,B11))+1)/2

20170522, 22:36 #8
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,923
 Thanks
 152
 Thanked 747 Times in 679 Posts
Todd,
I think we have this one licked by using a User Defined Function (UDF)
I added a new column (col O) with the UDF that will negate the need for the helper columns KN. In the sample, note the different resolutions in the new highlighted column O compared to the previous version's column N. They are the same except where there are multiple weekend overlaps, the numbers account for additional excluded hours.
Todd1.png
From the Time stamped start date, the UDF calculates the next weekend dates (Friday 17:00 thru Sunday 22:00). If the calculated weekend start date (Friday 17:00) is after the time stamped end date, there is no overlap, just the total hours (tstmp start date  tstmp end date) are returned, and the code ends. If the calculated weekend start date (Friday 17:00) is before the time stamped end date, there is overlap and the code performs an overlap calculation. The overlap is recorded to the variable "x".
The dates of the next weekend are calculated and again evaluated against the time stamped end date. If the weekend is prior to the end date, there is no further overlap, the total hours minus the excluded hours are returned, and the code ends. If there is again overlap, it is calculated and added to the accumulated value of x.
The next weekend is calculated and the process continues until the weekend falls beyond the time stamped end date indicating no further overlap. The total hours minus the excluded hours ("x") are returned, and the code ends
Place the following code in a standard module:
Code:Public Function OVERLAP(TSs As Range, TSe As Range) With WorksheetFunction I = 0 '7 DAY INCREMENT ADDED TO START DATE x = 0 'CUMULATIVE AMOUNT OF OVERLAP HOURS ' 'FIND FIRST SET OF WEEKEND EXCLUSTION DATES EXCLs = .Ceiling(TSs, 7)  1 + TimeSerial(17, 0, 0) 'FRI 17:00 EXCLe = .Ceiling(TSs, 7) + 1 + TimeSerial(22, 0, 0) 'SUN 22:00 ' 'LOOP UNTIL WEEKEND DATE (FRIDAY 17:00) EXCEEDS TIMESTAMP END DATE Do Until EXCLs > TSe x = x + .Max(0, .Min(TSe, EXCLe)  .Max(TSs, EXCLs)) 'OVERLAP I = I + 7 'INCREMENT EXCLs = .Ceiling(TSs, 7)  1 + I + TimeSerial(17, 0, 0) 'NEXT EXCL EXCLe = .Ceiling(TSs, 7) + 1 + I + TimeSerial(22, 0, 0) 'NEXT EXCL Loop ' 'RETURN TOTAL HOURS MINUS EXCLUSION HOURS OVERLAP = TSe  TSs  x End With End Function
Code:=IF(OR(C2="Closed",C3="New",B2>B3),"",OVERLAP(B2,B3))
Thanks for a very challenging and interesting post.
HTH,
Maud