Count if date is between two other dates (for entire columns of dates) - DIFFICULT
I need a graph which shows the number of delayed tasks over time based on a set of target and actual dates of completion.
Basically what I need to do is get a list of dates and the number of all tasks which were overdue on the respective date. The basis for this data is a table which lists all tasks with a column containing the respective targe date and another column containing the actual end date for each task.
Here's a simplified example:
Task ID Target Date Actual End Date #1 01/01/2012 01/02/2012 #2 01/02/2012 01/02/2012 #3 01/03/2012 01/06/2012 #4 01/04/2012 01/05/2012
The third column is not needed, I just added it to illustrate which columns were counted.
Date No. of Tasks overdue Task IDs of overdue Tasks 01/01/2012 0 - 01/02/2012 1 #1 01/03/2012 0 - 01/04/2012 1 #3 01/05/2012 2 #3,#4 01/06/2012 1 #3
It is important that the solution DOES NOT involve a huge amount of cells filled with similar formulas (which would make the file large and calculations slow) or any VBA macros.
Ideally it should be a formula that counts how many times a given value is both larger than the left and smaller than the right cell value of each row of two neighbouring columns.
I would really appreciate any help on this as its starting to give me serious headaches
Deuces
Hi Deuces,
I am not clear with your second table which appears to be the answerset you are looking for.
Completion for Task ID#1 was targeted at 01/01/2012 but completed at 01/02/2012.. hence on 01/01/2012.. this is one task over due and your table differs here.
Please correct me if I am not getting it correctly. Thanks.
Regards,
DILIPandey
<click on below 'star' if this helps>
DILIPandey
+919810929744
dilipandey@gmail.com
That depends on your perspective. As I see it, task #1 was not (yet) overdue on 01/01/2012 (since that's the due date). It doesn't really matter though - I could easily adjust this by replacing > with >= in the formula (once I have a formula to work withOriginally Posted by dilipandey
)
Thanks for your help, please let me know if this needs further clarification.
Hi Deuces,
Go the logic now... the part which I missed was "Over" in overdue....
I'll look into it and let you know. Thanks.
Regards,
DILIPandey
<click on below 'star' if this helps>
DILIPandey
+919810929744
dilipandey@gmail.com
Hi Deuces,
Below is the solution basis your data arrangement:-
Task ID Target Date Actual End Date Days Day#1 Day#2 Day#3 Day#4 Day#5 #1 01/01/2012 02/01/2012 1 02/01/2012 #2 02/01/2012 02/01/2012 #3 03/01/2012 06/01/2012 3 04/01/2012 06/01/2012 #4 04/01/2012 05/01/2012 1 05/01/2012
Use following formula in column "Days" and drag down:-
=E2-C2
Use following formula in column Day#1 and drag right and down:-
=IF($C2+IF(COLUMN(A$1)<=$G2,COLUMN(A$1),0)>$C2,$C2+IF(COLUMN(A$1)<=$G2,COLUMN(A$1),0),"")
Now to get number of tasks over due, arrange Date and Tasks as below table:-
Date Tasks 01/01/2012 0
Enter dates like 01/01/2012, 01/02/2012... under date column and use following formula under Tasks column :-
Where I2:R5 would be a range as shown above in first table (e.g., Day#1, Day#2 etc) and T2 is the date which you have just entered.=COUNTIF($I$2:$R$5,$T2)
Basically, here you are just using countif function to determin how many dates are appearing in the Day#1, Day#2 .... range.
Feel free to get back in case of any queries.
Regards,
DILIPandey
<click on below 'star' if this helps>
DILIPandey
+919810929744
dilipandey@gmail.com
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks