Plotting Numbers of Bugs Raised Each Day From a BugTracker in Excel
7th December, 2011 – 4:31 pmI had a requirement to plot the number of bugs raised on each day on a graph to show how many are raised. The bugs were stored in the very wonderful BugTracker.NET . The problem was I had a list of bugs with a date time.
|
ID |
MIN(dateUpdated) |
|
2 |
11/04/2011 09:10 |
|
4 |
12/04/2011 16:48 |
|
14 |
18/04/2011 21:03 |
|
15 |
20/04/2011 07:14 |
|
16 |
20/04/2011 20:30 |
|
17 |
20/04/2011 20:30 |
Here is how I solved it.
- Open in Excel and copy just the IDs and dates to a new sheet.
- In column three add the following formula (it formats the SQL datetime into a short date that Excel prefers– if you have access to the original DB then do this as a query – NOTE:- I’m a Brit so this is in UK Date format!!) =DAY(B2) & “/” & MONTH(B2) & “/” & YEAR(B2)
- I usually copy and do a Paste Special “paste as values” to clean down the Excel formulas into a new sheet called BugsByDate
- Find your min and max dates of all bugs by sorting the data or perhaps you know when the project started and ended)
- In a new data sheet enter “Date” in column A and “Bugs” in B. In A2 enter the lowest date and then drag the cell handle down until you get the desired date range.
- In B2 enter the formula =COUNTIF(BugsByDate!$A$2:$G$999,A2) – replace 999 with the number of bugs you have!
- Drag the handle down to sum up the bugs for each date.
- Highlight your data and insert a Scatter with Straight Lines and Markers
- Enjoy
Optionally you can also get the cumulative number of bugs – just use a formula in the next column which is =SUM($B$2:B2) – drag this down and the second B2 will increment – easy peasy!
Loading tweets...