Pivot Table Issues

How to Fix Excel PivotTable Dates for Opened and Closed Tickets

Kushani Nimanthika Oct 1, 2026 868 views

Question details

The user needs to configure an Excel PivotTable so that closed tickets are grouped into the actual month they were closed, rather than the month they were opened.

How to Fix Excel PivotTable Dates for Opened and Closed Tickets

Product: Microsoft Excel
Device & OS: not provided
Scenario: Generating a monthly tracking report in a PivotTable to compare the volume of opened versus closed tickets over time.
Observed behavior: Because both the opened and closed dates reside on the same row for a single ticket, the PivotTable groups the entire record by the opened date, causing closed tickets to appear in the wrong month.

Before you start

Ensure your dataset is formatted as an official Excel Table and verify that your 'Opened Date' and 'Closed Date' columns contain valid, recognizable date formats.

Solution 1Recommended

Use Power Query to Unpivot Date Columns

Restructure your source data into an event-based table using Power Query so the PivotTable can analyze opened and closed dates independently.

Standard PivotTables process one primary date field per row. By using Power Query to unpivot your date columns, you create a new structure where every ticket has two separate rows: one for the 'Opened' event and one for the 'Closed' event.

Load data into Power Query

Click anywhere inside your source data table. Navigate to the 'Data' tab on the Excel ribbon and click 'From Table/Range' to open the Power Query Editor.

Select the date columns

Hold down the Ctrl key and click the column headers for both your 'Opened Date' and 'Closed Date' columns to select them simultaneously.

Unpivot the selected columns

Go to the 'Transform' tab in the Power Query Editor, click 'Unpivot Columns', and select 'Unpivot Only Selected Columns' from the dropdown menu.

Rename the generated columns

Double-click the new 'Attribute' column header and rename it to 'Event Type'. Double-click the 'Value' column header and rename it to 'Event Date'.

Load to a new PivotTable

Go to the 'Home' tab, click 'Close & Load To...', select 'PivotTable Report', and click 'OK'. You can now place 'Event Date' in the Rows area and 'Event Type' in the Columns area.

Data Successfully Restructured: Your PivotTable will now correctly display closed tickets in the exact month they were closed, independently of when they were opened.

Solution 2

Manually Stack Data in a New Worksheet

Manually copy and align your data into an event-based format if you do not have access to Power Query.

Free Microsoft Office alternative

Manage Complex Datasets with WPS Office

If you frequently work with pivot tables and complex data reporting, WPS Office offers a fast, lightweight, and highly compatible spreadsheet tool to streamline your workflow without the heavy subscription costs.

1. Download and Install: Visit the WPS Office website and download the free installation package for your operating system.
2. Open Your Excel File: Launch WPS Spreadsheets and open your existing .xlsx ticket tracking file seamlessly.
3. Analyze Data: Use the built-in PivotTable features to quickly summarize and visualize your event data.

Free and lightweight alternative to Microsoft OfficeFully compatible with Microsoft Excel (.xlsx) files and standard formulasIntuitive PivotTable interface for quick and accurate date groupingFamiliar user interface ensuring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why does a standard PivotTable group closed tickets into the opened month?

A standard PivotTable evaluates rows as single entities. When you place 'Opened Date' in the rows area, the PivotTable groups the entire row (including the close date) under that specific opened month.

What is an event-based table structure?

An event-based table structure separates different dates (like opened and closed) into their own distinct rows rather than keeping them in separate columns on the same row. Each row represents a single action or 'event' tied to a specific date.

How do I group dates by month in a PivotTable?

Once your dates are in the Rows area of the PivotTable, right-click any date cell, select 'Group' from the context menu, highlight 'Months' (and 'Years' if applicable), and click OK.

Excel & Data Problems