We help IT Professionals succeed at work.

Excel Pivot Table - combining two date field ranges together

854 Views
Last Modified: 2018-02-02
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?
Comment
Watch Question

SDM Mobile
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

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
KoenSDM Mobile
CERTIFIED EXPERT

Commented:
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...
KoenSDM Mobile
CERTIFIED EXPERT

Commented:
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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions