Forum Discussion
Excel Date Range Trend Graph Data Misrepresenting.
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
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.
- https://docs.google.com/spreadsheets/d/1xLKmagbA9WNcjt6Zm2mWNztUUJNuplcb/edit?usp=sharing&ouid=114563258067065292626&rtpof=true&sd=true
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.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?"
- 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?
