Excel Pivot Table - combining two date field ranges together

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?
Dilen PatAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

KoenChange and Transition ManagerCommented:
I usually plot two bars, one for tickets opened and one for tickets closed... so the range would be the two columns (resulting in all dates to be present), the X axis having all the dates, and the Y axis the number of tickets. (look at the attached example)

Or is that not what you want?

To do that, you need to create a new table, having the dates in the first column, and than a countif in the second and third column, where column b would be countif(opened on the day in column a) and c would be countif(closed on the day in column a)
then you plot those on a nice bar graph
EE4.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dilen PatAuthor Commented:
The graph I had in mind was a line or area plot preferrentially. You managed to plot your graph on your second sheet by that table which shows the counts you calculated for each date. Fair enough, but how have you combined the start and end dates and extracted only the unique ones for column A of that second sheet? I have many open and closed incidents to the point where manually doing this isn't really an option

The count isn't an issue, the pivot table does that easily. What I can't seem to do with it, is create that column A of yours which holds ALL the unique dates from both start and end date columns
0
KoenChange and Transition ManagerCommented:
I didn't. I just put all dates there...
You could copy your start dates (all of them) then copy your end dates underneath (also all of them) and then remove duplicates and sort... that would leave you with a column A with only the dates present in your sheet...
0
KoenChange and Transition ManagerCommented:
I dunno if it is ok, for me to select my own answer... but I was the only participant and I feel I have answered the question
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Pivot Tables

From novice to tech pro — start learning today.