Microsoft 365 and Office | Excel | For business | Other
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
Glad to hear you had already resolved it by Power Query:)
Pivot table in Excel is showing the closed tickets in the month they were opened.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Anonymous
I am trying to create a pivot table that shows the # of tickets Opened, # closed, and what month they were opened or closed in. There are 45 tickets opened, and 5 closed. The open tickets are appearing under the correct month the closed ones are not.
My data:
| Opened-Date | Closed-Date |
|---|---|
| 5/3/2024 | |
| 5/3/2024 | |
| 5/2/2024 | |
| 5/2/2024 | |
| 5/1/2024 | |
| 5/1/2024 | |
| 4/29/2024 | |
| 4/23/2024 | |
| 4/23/2024 | |
| 4/11/2024 | |
| 4/10/2024 | |
| 4/8/2024 | 5/7/2024 |
| 4/3/2024 | |
| 4/3/2024 | 4/30/2024 |
| 4/3/2024 | |
| 3/29/2024 | |
| 3/26/2024 | 5/2/2024 |
| 3/22/2024 | |
| 3/20/2024 | |
| 3/18/2024 | |
| 3/12/2024 | |
| 3/11/2024 | |
| 3/4/2024 | |
| 2/22/2024 | 5/3/2024 |
| 2/21/2024 | 4/3/2024 |
| 2/7/2024 | |
| 2/6/2024 | |
| 2/6/2024 | |
| 1/31/2024 | |
| 1/8/2024 | |
| 1/4/2024 | |
| 1/2/2024 | |
| 12/18/2023 | |
| 12/7/2023 | |
| 11/29/2023 | |
| 11/20/2023 | |
| 11/20/2023 | |
| 10/12/2023 | |
| 9/26/2023 | |
| 9/18/2023 | |
| 8/31/2023 | |
| 8/30/2023 | |
| 6/22/2023 | |
| 6/15/2023 | |
| 5/19/2023 |
Pviot table fields are attached. What am I doing wrong?
Microsoft 365 and Office | Excel | For business | Other
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
Anonymous
Answer accepted by question author
Anonymous
You may need to change your raw data structure to show the data correctly.
10 additional answers
Sort by: Most helpful
-
Anonymous
2024-05-08T15:39:16+00:00 ok, thanks!
-
Anonymous
2024-05-08T15:27:24+00:00 You are correct. Your new data structure will be easy to show closed and opened date in povit table.
-
Anonymous
2024-05-08T15:19:31+00:00 So they are the same ticket with two different dates. So I would have to show one ticket twice in my spreadsheet with an open date and a close date? That seems so redundant:
Ticket 1 opened-date 4/7/2024 Ticket 1 closed-date 5/3/2024 Ticket 2 opened-date 11/1/2023 Ticket 2 closed-date 4/29/2024 Is that correct?