Simple Macro to find average time difference by category

Hi -

Hoping someone can help me automate a report I create regularly - I think it should be very easy but the VBA is a tad above my (nearly nonexistent) skill.

The example spreadsheet is attached (the results sheet is added by me).

Basically, I would like to know the average difference between Posted Date and Reply Date columns for each Channel. It would be awesome if the output were on a different spreadsheet as in the attached.

Alpha - 00.01.12.14
Beta - 00.02.19.16
Gamma - 00.12.07.00
etc.

Note - not every row has a "Reply Date" - if it doesn't, that row should be omitted for calculation. In other words, only rows that have a Reply Date should be included in the average.

Columns might not be in the same place each time, but the headers are always the same.

Would be grateful for any help with this.

Thank you!

Andrey
Sample-time-diff-report.xls
LVL 6
andreyman3d2kAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Herbds7Commented:
0
FaustulusCommented:
Please try the code in the attached workbook. Run the procedure "WriteAverageResponseTimes()". You can transfer the code to your own project by dragging the module 'AverageMan' into your project in VBE's Project Explorer window while both workbooks are open.

Before you run the code in your own workbook please check these three constants at the top of the above mentioned procedure.

    Const InputSheetName As String = "Inbound.CSV"
    Const OutputSheetName As String = "AVG Response Time"
    Const Captions As String = "Channel,Posted Date,Reply Date"

The InputSheet must have the name specified by the constant.
The Output sheet will be named as specified by the constant. Note that any sheet existing my this name will be deleted and replaced by the newer result.
The constant 'Captions' has the names of the column headers. It doesn't matter in which sequence they appear in the source (input) worksheet, but if they don't exist the program won't run.
Also note that the sequence of the captions within the constant's string must match the sequence specified for the index in the enum at the top of the code sheet:-
Private Enum Nwc                ' Col() index
    NwcChannel
    NwcPosted
    NwcResponse
End Enum
Here it is Channel column caption, Posted column caption, Response column caption.

The final list will be sorted by Channel name. The response time will be shown as minutes and seconds. If you need it to be hours, minutes, seconds it's not a big deal. Just say so.

The code is tested but the result isn't. So, it might run but not give the correct result. Please test before you deploy.
EXX-131012-Average-Response-Time.xls
0
andreyman3d2kAuthor Commented:
Hi Faustulus,

Thanks very much for this - it seems to do the trick on the sample sheet I provided, but when I run it on the actual data I get "Runtime Error 13 - Type Mismatch" on this line:

Response = CDbl(.Cells(R, Col(NwcResponse)).Value)

Do you know what the issue might be?

Also, would it be possible to change the date type to show hh:mm:ss as you mentioned?

Thanks again,

Andrey
0
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

FaustulusCommented:
To change the output format please look for this line of code
.NumberFormat = "[mm]:ss"
and change it into
.NumberFormat = "hh:mm:ss"

Open in new window

The Type mismatch error is most probably caused by your data. Perhaps a time has been entered as a text string instead of a proper time.
When the error occurs select Debug from the dialog box. The line you mention will be highlighted. Hover the cursor over the R in .Cells(R, Col(NwcResponse)).Value. the number which will be displayed is the row number where you the error occurred. Select Col(NwcResponse) to get the column number. In this way you have identify the culprit exactly and see what's wrong with it. The error message indicates that the value in that cell can't be converted into a number of Double precision - for example a string.
There are ways of converting times written as text strings into proper time values. If the error is rampant in your data we might build such a converter even into the code. That wouldn't be worth the effort if it is an error that you can exclude form your data for the future.
0
andreyman3d2kAuthor Commented:
Hi, apologies for the delay in reply! I followed the steps you suggested - the error is in the "reply date" column, but the cell in the row it identified was blank! What's strange is that there are other blank cells that appear several rows above and below in that column, so I am not sure why that particular cell is triggering the error. I clicked into it, and it is indeed blank.
I tried deleting it the row and others around it, but then it kicked up the same error for another row int he same column, also with a blank cell, surrounded by other blank cell.
0
FaustulusCommented:
The problem is that a proper value for the Response variable can't be extracted from a blank cell. I will look into this later today when I have access to VB and Excel. It isn't a big problem. Just needs precise programming.
0
FaustulusCommented:
Please replace the existing with these two lines of code in the procedure 'GetAverage'.
                Posted = CDbl(Val(.Cells(R, Col(NwcPosted)).Value))
                Response = CDbl(Val(.Cells(R, Col(NwcResponse)).Value))
To tell you the truth, this is what it should have been to begin with. Sorry for the trouble this has caused you.
0
andreyman3d2kAuthor Commented:
Thanks - and don't be sorry - you're helping me immensely!! : )

I implemented the change, and the code ran without errors- however this time, all the results are incorrect! Most came out to zero (which they should not be), and several came out to 94370400:00 (not sure what the significance is).

Just to be sure, I made the revisions to the code in the example workbook you had uploaded, and all the results came out to zero (previously they had all been correct).

Do you have any idea what the issue might be?

Thanks!

Andrey
0
FaustulusCommented:
Hello Andrey,
This is caused by the nature of your input data, i.e. the time values. They aren't Excel Date/Time values. It seems that you have different kinds of input data. Excel could be made to examine and convert them to the required format before processing. However, we would need a proper process by which to do it. The problem seems to be that you have several sheets which are different from each other in unexplained ways.
The fastest way would be to first settle on one, get that one up and running, then see what is the difference on the next one and adapt the code to be able to work on both.
Please post the worksheet which you wish to do first.
0
andreyman3d2kAuthor Commented:
Make sense - attached is the actual workbook I am running the macro on. (Please note the sheet name has been changed, and the Posted Date column is now Posted On. I had made these changes in the code when I ran it).

Thanks again!!

Andrey
inboundtest.xlsx
0
Herbds7Commented:
Excel 2010, Tables
Some of your empty cells are not blank.
No macro.
http://www.mediafire.com/download/b6r6klk6kuwjv75/10_09_13.xlsx
0
FaustulusCommented:
Hello Andrey,
There is nothing wrong with your data. It's just that my handling of blank cells doesn't meet with VB's approval. I have re-written the code in that regard and it now runs fine with the data you provided. Please replace the function 'GetAverage' that you have with this one.
Private Function GetAverage(ByVal Channel As String, _
                            ByVal Rf As Long, _
                            ByVal Rl As Long, _
                            Col() As Long, _
                            WsS As Worksheet) As Double
    Dim R As Long
    Dim Times As Double
    Dim Counter As Integer
    Dim Posted As Double
    Dim Response As Double
    
    With WsS
        For R = Rf To Rl
            If StrComp(.Cells(R, Col(NwcChannel)).Value, Channel, _
                       vbTextCompare) = 0 Then
                On Error Resume Next
                Posted = CDbl(.Cells(R, Col(NwcPosted)).Value)
                Response = CDbl(.Cells(R, Col(NwcResponse)).Value)
                If Response And (Posted > 0) Then
                    Times = Times - Posted + Response
                    Counter = Counter + 1
                End If
            End If
        Next R
    End With
    GetAverage = Times / Counter
End Function

Open in new window

I attach the entire updated project for your easy reference.
EXX-131012-Average-Response-Time.xls
0
andreyman3d2kAuthor Commented:
Hi, I just tested this and getting strange results - the times are coming out incorrectly, and some of them are coming out as large negative numbers (-0.51956124545458). I also tested manually on the spreadsheet you attached for the Alpha channel, and the result I got was 4:34:25 (instead of 3:59:31 the macro gave). Not sure what might be causing the calculation errors - do you have any idea?

Thanks,

Andrey
0
FaustulusCommented:
I'm travelling. Therefore my response will be a bit slow. Sorry about that.
I'll look at the results very closely this evening. Unfortunately, you didn't tell me the row where you had a negative or wrong result. But I do presume that you are looking at the workbook I last posted.
0
andreyman3d2kAuthor Commented:
Thanks - I ran it on the workbook I last uploaded, which has the larger data set.
0
FaustulusCommented:
I really tried to get around to your problem earlier. It was on the top of the pile all along, but events got the better of me.
I tested my code very carefully and found that section we were looking at was still not performing as expected and re-wrote it. Look for this code in the procedure 'GetAverages',
'''                With .Cells(R, 8)
'''                    .Value = Response - Posted
'''                    .NumberFormat = "0.000000"
'''                End With
You can delete this code when you are done with the subject. I used it to write the individual time differences into column H (=8) to give me a visual check on what was included in the calculation. Then I used an Excel worksheet formula to draw an average based on those differences to check my calculated total on the other worksheet. You have those results in column D.

It really looks like there should be no more errors now.
Regards,
Faustulus
EXX-131103-Average-Response-Time.xls
0
andreyman3d2kAuthor Commented:
Cool! I think it's getting really close! On the actual data, it just seems to be mis-assigning the time to the channel.

I have channels named, for example @Alpha and @Alpha247 - I noticed the time for @Alpha was incorrect when I did a manual check - but noticed that the time I got was actually in the @Alpha247 row, and the @Alpha247 time was in the @Alphabeta channel etc.

I also have a situation where the channels is Alpha and the time for it is going to @Alpha

Do you have any idea why it's mis-aligning the times to the names? All the times seem to be correct and the names are all there - just not aligned properly.

Thanks again very much, I am really grateful for the time you're spending on this!

Best,

Andrey
0
FaustulusCommented:
Hi Andrey,
Thank you for your excellent description of the error. It lead me straight to the fault in the code. It is in the sorting. Actually, the result is computed correctly but then, on sorting, the channel names are moved while the results stay in place. Please replace the existing procedure 'SortChannels' with this one:-
Private Sub SortChannels(WsT As Worksheet)
    
    With WsT.Sort
        With .SortFields
            .Clear
            .Add Key:=WsT.Columns(1), _
                 SortOn:=xlSortOnValues, _
                 Order:=xlAscending, _
                 DataOption:=xlSortNormal
        End With
        .SetRange WsT.Range(Columns(1), Columns(2))
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Open in new window

or just replace this one line of code,
.SetRange WsT.Range(Columns(1), Columns(2))

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
andreyman3d2kAuthor Commented:
Thanks so much for this - I truly appreciate the time you spent!! It is tremendously helpful, and I just wish I had more points to give!! : ))

With gratitude,

Andrey
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.