Plotting Numbers of Bugs Raised Each Day From a BugTracker in Excel

7th December, 2011 – 4:31 pm

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

  1.  Open in Excel and copy just the IDs and dates to a new sheet.
  2. 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)   
  3. I usually copy and do a Paste Special “paste as values” to clean down the Excel formulas into a new sheet called BugsByDate
  4. Find your min and max dates of all bugs by sorting the data or perhaps you know when the project started and ended)
  5. 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.
  6. In B2 enter the formula =COUNTIF(BugsByDate!$A$2:$G$999,A2)  – replace 999 with the number of bugs you have!
  7. Drag the handle down to sum up the bugs for each date.
  8. Highlight your data and insert a Scatter with Straight Lines and Markers
  9. 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!

Post a Comment