Excel Date Range Trend Graph Data Misrepresenting.

New Contributor

I am having trouble working out how to represent a trend in excel only using an Open and Closed date range.
I have approximately 350 rows of date ranges and I need to show a trend between when a finding opened versus when it closed (i.e resolved) I can't seem to get an excel graph to represent that data effectively.

Any help or direction where I can find help would be greatly appreciated.

5 Replies

@haigohanian 

You could help us help you by saying a bit more about how your rows of data are arrayed. What are the columns. Is there a start and end date in each row? Etc. 

Ideally, post a copy of the workbook itself on OneDrive or GoogleDrive and grant access. Just make sure you're not revealing any confidential or proprietary data.

I can't post it here as it's restricted data on my work laptop. I need to produce a report that shows (visually on a graph) the trend between when a finding was open versus when it closed, so I have various dates that span years. So an example would be a finding open on 21/05/21 and closed on 21/3/2022, I have approx 350 of these in total and I'm struggling to understand how to input this data. At the moment it just doesn't make sense when I use the standard line or column graph. I need to really understand if how I am inputting the data is wrong (using graph select data and highlighting the dates column) or if perhaps this isn't really something that I can do within excel graphs/charts. I have been stuck on this for days and I can't seem to work it out - I could perhaps replicate the data with fictitious data and post it here if that would help?
https://docs.google.com/spreadsheets/d/1xLKmagbA9WNcjt6Zm2mWNztUUJNuplcb/edit?usp=sharing&ouid=11456...

So I have replicated it here as a test with different data, essentially this is what I am working with but with 350 rows as you will see the chart doesn't really give an insight into the trends between open and closed dates. This is where I am struggling. The overdue days aren't really relevant I just put them in to give a better understanding of the data I am working with.

To note I have been teaching myself more about excel recently and have learnt a lot, however, with charts and data analysis I am still very much a beginner.

Thanks for taking the time to reply.

@haigohanian 

 

You don't actually say what "trend" you're seeking. I'm making an assumption here that it's a trend in the length of time between Open and Close--duration of "Open"--so I've added a column that just calculates that length of time. The whole table can then be sorted based on either the start dates or the end dates. The sort by start date creates a line graph as shown here, and a clear trend is apparent. The question is simply: "Is this what you are looking for?"

mathetes_0-1656170280485.png

 

Apologies for a trend based on when it opened versus when it closed so that we can see how long it's taking to close the findings from when it first opened. I have attached a picture of what I have tried to do with a much smaller section of data. The blue line represents when it opened the orange line when it closed by the month https://drive.google.com/file/d/1r7WxB28v5HT38dduq0JnRHoOab0KKY8y/view?usp=sharing