Results 1 to 8 of 8
  1. #1
    New Lounger
    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.
    Attached Files Attached Files
    Last edited by FarmerTodd; 2017-05-18 at 16:09.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,793
    Thanks
    139
    Thanked 705 Times in 639 Posts
    Farmer,

    Could you post a macro stripped .xlsx file sample?

  3. #3
    New Lounger
    Join Date
    Feb 2017
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    macro stripped .xlsx file sample?
    I'm not sure what you mean by this - there are no macros in the file I uploaded. Here is the file in .xlsx, but the file will always be produced in .xls because of the output from QC.
    Attached Files Attached Files
    Last edited by FarmerTodd; 2017-05-19 at 11:17. Reason: Adding File

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,793
    Thanks
    139
    Thanked 705 Times in 639 Posts
    We were using VBA for the way we used to calculate resolution time
    Since .xls files can run code, they are generally considered high risk files. Thanks for posting the .xlsx file. If I can come up with a resolution, I will post it in .xls format.

    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

  5. #5
    New Lounger
    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,B11-B10)

    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.

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,793
    Thanks
    139
    Thanked 705 Times in 639 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)
    Format as Custom: m/d/yyyy h:mm

    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)
    Format as Custom: m/d/yyyy h:mm

    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))
    Format as Custom: [hh]:mm

    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),"",B3-B2-M2)
    Format as Custom: [hh]:mm

    I am working on the conditional formatting now

    Maud
    Attached Files Attached Files

  7. #7
    New Lounger
    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:

    =((B11-B10)-(NETWORKDAYS(B10,B11))+1)/2

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,793
    Thanks
    139
    Thanked 705 Times in 639 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 K-N. 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
    In the cell of row 2 in any adjacent column (Column O in this instance), place the following formula and copy down:
    Code:
    =IF(OR(C2="Closed",C3="New",B2>B3),"",OVERLAP(B2,B3))
    The conditional part of the formula prevents a calculation attempt between different project IDs. Helper columns K thru N are no longer needed as their calculations are integrated into the UDF.

    Thanks for a very challenging and interesting post.

    HTH,
    Maud
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •