Solved

Excel Pivot Table - Totals Aren't Working

Posted on 2011-03-25
13
273 Views
Last Modified: 2012-06-27
Attached is an Excel file with a macro that creates a Pivot table and the other file contains the Pivot table itself.  I was given this on a previous question, but didn't notice that the totals don't work.  After trying some things manually, it said I couldn't change this part of the pivot table.  I can make it work if I turn on the macro recorder and create the pivot table from scratch, but then it wouldn't include the start and end times like I want.  Thank you.
logonEvents.csv
LogonReport.xls
LogonReportStarter2.xls
0
Comment
Question by:rvfowler2
  • 6
  • 6
13 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35229959
Could you clarify what exactly is wrong with the pivot table as it is in the workbook you provided?
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 35232464
The totals are wrong.  They don't add up to the sum of the daily hours the person put in for the week.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35232525
That's just a formatting issue - you need to format the Hours field to display as [hh]:mm rather than hh:mm
HTH
Rory
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 35232539
In other words, change this line:
.PivotFields("Total Hours").NumberFormat = "hh:mm"

Open in new window

to this:
.PivotFields("Total Hours").NumberFormat = "[hh]:mm"

Open in new window

0
 
LVL 2

Author Closing Comment

by:rvfowler2
ID: 35232613
Thanks, that worked.  Missed an easy one.
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 35232682
Actually, it fixed that issue, but if you do it, I have tons of other issues.  Whoever helped me before totally messed up what I had that was working.  All I asked for in a previous question was to add a start and ending time.  I think I'm going to manually record the process and start over.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35232911
If you are referring to this PAQ: http:/Q_26831553.html then that was me. Since you gave no feedback or explanation of a problem then, or now, not much I can do about it. (I don't really see how formatting the output causes you a problem - the values are still the same).
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 35232997
Just make the change as you indicated top [hh], then run the report.  I've included the data file that I cleaned up a bit this a.m.  I'm still looking through it, but wondering if your code simply calcs the elapsed time between beginning and end times rather than add up the elapsed times because sometimes people log in and out a couple of times per day.

 Copy-of-logonEvents.csv
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35233175
The code adds up the same formulas you were using originally. Can you give me an example where it is wrong? as far as I can see, the formulas total to 606:57 hours of work, as does the pivot table.
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 35233371
Yes, sorry, for the most part, it seems that I need to so some data cleanup.  It would be nice if the code could account for orphaned logon and logoffs.  I did run the old code which I am copying below, and it came out just a bit better than your code in producing totals closer to accurate (compare Randy to Randy), but there were still problems.  See attached report as the result of the old code.
Sub FormatLogonReport_OLDER()
'
    ChDir "R:\LogonScript"
    Workbooks.Open Filename:="R:\LogonScript\Copy of logonEvents.csv"
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:="R:\LogonScript\LogonReport.xls", FileFormat _
        :=xlExcel9795, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
        False, CreateBackup:=False
    Application.DisplayAlerts = True

'ENTER HEADERS
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Rows("1:1").Select
    Selection.Font.Bold = True
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Userid"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Workstation"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Day"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Date"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Time"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Event"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Hours"
  
    Cells.Select
    Cells.EntireColumn.AutoFit

    Columns("A:G").Select
    Range("G1").Activate
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("D2") _
        , Order2:=xlAscending, Key3:=Range("E2"), Order3:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal

'FORMAT COLUMN AND ENTER HOURS CALC
    Columns("G:G").Select
    Selection.NumberFormat = "h:mm;@"
    Dim lrow As Long
    lrow = Cells(Cells.Rows.Count, "f").End(xlUp).Row
    Dim rng As Range, cell As Range
    Set rng = Range("F2:F" & lrow)
    For Each cell In rng
        If UCase(cell.Value) = "LOGOFF" And UCase(cell.Offset(-1, 0)) = "LOGON" _
            Then cell.Offset(0, 1).Formula = "=(d" & cell.Row & "+E" & cell.Row & ")-(d" & cell.Row - 1 & "+E" & cell.Row - 1 & ")"
    Next cell
    
    'INSERT THE PIVOT TABLE
    Windows("LogonReport.xls").Activate
    Cells.Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'Copy of logonEvents'!$A:$G").CreatePivotTable TableDestination:= _
        "'[LogonReport.xls]Copy of logonEvents'!C9:C14", TableName:="PivotTable1", _
        DefaultVersion:=xlPivotTableVersion10
        
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("Userid", _
        "Day")
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Hours")
        .Orientation = xlDataField
        .Caption = "Sum of Hrs Worked"
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Userid")
        .PivotItems("administrator").Visible = False
        '.PivotItems("temp").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Day")
        .PivotItems("(blank)").Visible = False
    End With
' FINAL FORMATTTING
    Columns("K:K").Select
    Selection.NumberFormat = "[h]:mm"
    ActiveWorkbook.ShowPivotTableFieldList = False
    Application.CommandBars("PivotTable").Visible = False
    Columns("I:I").Select
    Selection.Font.Bold = True
    Columns("I:K").EntireColumn.AutoFit
    Range("J2:K2").Select
    Selection.Font.Bold = True
    ActiveWorkbook.Save
    
    'Windows("LogonReportStarter.xls").Activate
    'ActiveWindow.Close
    
End Sub

Open in new window

LogonReport2.xls
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35233437
Small point, but the only difference there is in the sorting, which is your code, not mine - I only added the columns to the pivot table. :)
What do you want to do with orphaned items? Ignore them? If so, you could add a check to see if the Day (or date) is the same as the row above in addition to checking for LOGON followed by LOGOFF?
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 35233696
Yes, I think it's best to ignore the orphaned items.  If you only added the start and end times columns, not sure why the totals of the two reports would be different in some places.  As I mentioned you could compare Randy with Randy on the two reports.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question