Display correctly date and hour in a pivot table for an excel report

Most of data that I am analysing have columns with a date and time so when I create a pivot table that include those columns, excel doesn’t show correctly the information. To display correctly, I need to do some setting changes. Imagine that I have this data:

excel pivot table

When I create the pivot table, I got this:

excel pivot table

After doing some changes, I will get this:

excel pivot table

If you have the same issue, you will need to do:

  • 1. Create the pivot table then put all fields in “rows”
    excel pivot table
    NOTE: when I put “start” and “duration”, automatically excel will add “hours, days, hours2 and days2”, remove them from “rows”
  • 2. Right click on the pivot table to select “pivottable options”
    excel pivot table
    a. In “totals & filters”, uncheck those options to not show all totals
    excel pivot table
    b. In “display”, uncheck/check as shown
    excel pivot table
    NOTE: if all go well, I will have this:
    excel pivot table
  • 3. Right click on the column A of the pivot table to select “field settings”
    excel pivot table
    a. In “subtotals & filter”, select “none”
    excel pivot table
    b. Do the same thing for the 2 other columns
  • 4. Right click on the column B of the pivot table to select “ungroup”
    excel pivot table
    a. Do the same thing for the column C (if need be the column A also but for this example no need)
    b. Adjust the columns to display it correctly
    NOTE: if all go well, I will have this:
    excel pivot table
  • 5. Display the title
    a. Click anywhere in the pivot table
    b. Click “analyse -> field headers”
    excel pivot table

Interesting Management