troubleshooting Question

Excel Pivot Table - combining two date field ranges together

Avatar of Dilen Pat
Dilen Pat asked on
Microsoft OfficeMicrosoft Excel* Pivot Tables* pivot chart
4 Comments1 Solution865 ViewsLast Modified:
I have an Excel based incident log which has two date columns - 1) the date an incident was raised and 2) the date an incident was closed (fixed). Both dates are mostly independent of each other. For reporting purposes, I wanted to make pivot tables and charts. I want to have a chart of frequency vs date which compares the number of incidents raised with the number of incidents closed

Currently, I'm just using the date the incident was raised as the X axis, but this isn't appropriate as that means it will only plot data points from the closed incidents that HAPPEN to also close on the same date as when an incident was raised.What happens on days where incidents are closed but no new ones are raised? These wont be plotted.

The X axis either has to combine all the unique dates from both the start and end dates, or it needs to include every date since the oldest incident (from 3/Nov/2016 to today). Given what the columns mean, it doesn't make sense for me to combine both columns together.

It seems I need to find a way to do either of the two options on the pivot level. It seems inappropriate to mess up my incident log by adding an extra column to try and fix this unless it can be done on a different worksheet? But then how would it be combined to the pivot table source data

Any advice?
ASKER CERTIFIED SOLUTION
Koen
SDM Mobile

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros