Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

Excel Pivot Table - Totals Aren't Working

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
rvfowler2
Asked:
rvfowler2
  • 6
  • 6
1 Solution
 
Rory ArchibaldCommented:
Could you clarify what exactly is wrong with the pivot table as it is in the workbook you provided?
0
 
rvfowler2Author Commented:
The totals are wrong.  They don't add up to the sum of the daily hours the person put in for the week.
0
 
Rory ArchibaldCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rory ArchibaldCommented:
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
 
rvfowler2Author Commented:
Thanks, that worked.  Missed an easy one.
0
 
rvfowler2Author Commented:
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
 
Rory ArchibaldCommented:
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
 
rvfowler2Author Commented:
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
 
Rory ArchibaldCommented:
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
 
rvfowler2Author Commented:
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
 
Rory ArchibaldCommented:
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
 
rvfowler2Author Commented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now