Once again we have reports that the system has chucked out that need a lot of work.  In this example we are reporting the turn-around-times for a monthly report.  In this example, the Cerner Powervision app gives us a report on each tests’ TAT.  This report is exported to Excel and the user counts how many tests exceeded TAT, and calculates the percent meeting target TAT. TEDIOUS!  Excel macros to the rescue:

Instead of making this into a full-blown tutorial I will just list the sticking points and post the macro.  If you have any questions just add a comment.

What to do when you can’t use Personal.xlsb to store your macros: (only for people using Personal.xlsb otherwise skip this)

The Powervision app exports the reports to Excel, but it’s not loading my Personal macros file.  This is because it’s running a Citrix Excel (I’m guessing) instead of using my pc’s excel.  For this you just have to put your macro code into a workbook, and then load that workbook with the Citrix excel along with the exported report.  Your macro code can still do everything it needs to, having Personal macros is just a convenience.  I have named my workbook “ProcessTATexportsMacroXl2003.xls”. It doesn’t actually have or import any data, it just contains my macro code.

The app exports each report to a separate workbook, this leads to another problem:

How to access sheets from a different workbook:

This isn’t really a problem but most are used to working at the sheet level in Excel. You just have to step back to the Workbook level.  Here’s how:

Each workbook that is loaded in Excel is given an index number. So we just cycle through the numbers:

    Dim intBook As Integer
    
    For intBook = 1 To Application.Workbooks.Count
        With Workbooks(intBook).Sheets(1)
 	 'Do whatever you need to do with sheet here
        End With
    Next intBook

 

Great. Next problem, the report gives the times in a DAYS HOURS:MINUTES format like this 0 00:00

That leading 0 is going to be troublesome. We can just cut it off. (If you wanted to be super careful you could make sure it’s not 0 and if not add it to your calculation).

Right(Cells(intRow, 6).Text, 5

 

Next problem, we have a list of times in Excel and we need to get the average time. The Excel AVERAGE function doesn’t work. We can use the datediff function to give us total minutes instead of hours and minutes.

How to calculate elapsed time when given hours:minutes in Excel:

This is also

how to calculate an average time from a list of times.

        Dim intFinalRow As Integer
        intFinalRow = Range("A65536").End(xlUp).Row

        For intRow = 1 To intFinalRow

                'intTime = CInt(Trim(Replace(Replace(Cells(intRow, 6).Value, ":", ""), " ", "")))  'WRONG!
                'This seems like it would work, just replace out all the colons and commas and get to the raw value, BUT
                'if the time is over an hour you get 1:15 which results in: 115.  Do it the right way and treat it as a date
                'you will get the correct number of minutes: 75
                strTime = DateDiff("n", 0, Right(Cells(intRow, 6).Text, 5))
                
                'Add a cell next to it with the tat time in minutes.
                Cells(intRow, 7).Value = strTime

        Next intRow

    'Now the Excel average function will work with our list of times since they are a whole number in minutes.
    Cells(intFinalRow + 1, 7).FormulaR1C1 = "=AVERAGE(R[-" & intFinalRow - 2 & "]C:R[-1]C)"

The last problem we run into is that one of the sheets contains TAT’s from both PT’s and aPTT’s, so we can’t just loop through the whole thing like the others. To solve this I added some checking and saved the beginning and ending row of each set. To see this and the entire working example code download the example file below.

ProcessTATExample.zip