• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Issue Register - last 30 days' formula

Larph

New Member
Hi all


I'm attempting to create an issue register that will check the last 30 days' activity (open and closed) much like Chandoo's example.


I have racked my brains and I cannot configure the formulas to track the last 30 days!

I've uploaded the file - any advice is much appreciated.

http://www.mediafire.com/file/6d0qm7qqcrx9obs/ISSUE_REGISTER_v1.xls


Cheers!
 
Larph

Have you tried using the Data table features of Excel

Select Area

Insert Table

That will allow you to show last 30 days and Open/Closed activity interactively and sorted


Also your download link isn't working?

you may not have authorised the file for downloading
 
Hi Hui


Try this one: http://www.mediafire.com/?6d0qm7qqcrx9obs

Should be working as it's showing 2 downloads; not sure what I'm doing wrong.


the reason I don't want to use a data table to track open/closed is because I want to implement a chart (possibly for use in a dashboard) that will show open vs closed issues for a rolling 30 days - much like here: http://chandoo.org/wp/2009/09/08/issue-trackers/


Cheers
 
O7: =SUMPRODUCT(1*($J$7:$J$306<=$N7)*($J$7:$J$306>=$N7-30),(K$7:K$306))

P7: =SUMPRODUCT(1*($J$7:$J$306<=$N7)*($J$7:$J$306>=$N7-30),(L$7:L$306))

and copy both down


If you only want to chart the last 30 days you can delete the Range N37:p306
 
Thank you for your kind response, Hui.


I've tried your formula as suggested, but for some reason the sheet is still not recognising and counting open/closed issues for the last 30 days.


(I've uploaded the amended file: http://www.mediafire.com/?d2p41tsopy3g2gz).


Any suggestions?


Cheers
 
You had a mixture of 2010 and 2014 dates in different places

The Dates in Column F and J7

Make them all the 2010
 
Thanks Hui - not sure how I managed to get a mixture of dates in.

I've straigthened them out, and the formula you suggested works up until a point - where it reads '9' under issues opened for dates that haven't been recorded.


Any ideas? (v3: http://www.mediafire.com/?73b67ahmxacls05)
 
It will stay at 9 & 5 until the sum of the previous 30 days goes up or down

Add more data and then change the dates to see what is happening
 
Back
Top