Excel Pivot Table - combining two date field ranges together

Dilen Pat
Dilen Pat used Ask the Experts™
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?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SDM Mobile
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


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

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

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial