Avatar of Dilen Pat
Dilen Pat
 asked on

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?
* Pivot Tables* pivot chartMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Koen

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Koen

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Dilen Pat

ASKER
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
Koen

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...
Koen

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck