Count tasks outstanding for more than x no. days, or between x and x no. of days

[karenlb]

Hi all

I have a project task list, and I am trying to produce a dashboard summary sheet. One of the items would be to count the number of tasks which remain uncompleted against the following criteria

Issues open longer than 90 days

Issues open between 60 and 89 days

Issues open between 30 and 59 days

Issues open between 15 and 29 days

Issues open for 14 days or less


Any uncompleted task has the status "actioned" (don't ask why it's not open!!

I have named ranges which are "issueopendate" and "issuestatus". When a task is completed the status is changed to "closed" and a date entered in a separate column


We also have a date cell (C5) which is entered manually and allows us to change all the graphs on the dashboard to whatever period we like.


I have got as far as =SUMPRODUCT(--(issuestatus="actioned"),--(actionopendate<=C5))

This counts the total number of issues raised before the date in C5


Now I'm stuck. I would still like to use the C5 date so that we can see what the project was like as at a certain date in time, but don't know how to then count dates between ranges..


Am I making life too complicated for myself....it's giving me a headache

 

[vijaySharma]

Karenlb,


Welcome to Chandorg forums...


are you saying this...


=SUMPRODUCT(--(issuestatus="closed"),--(actionopendate<=C5))

=SUMPRODUCT(--(issuestatus="pending"),--(actionopendate<=C5))

=SUMPRODUCT(--(issuestatus="approved"),--(actionopendate<=C5))


And use this data to create a new graph to show the progress...


~VijaySharma

 

[karenlb]

Hi


I already have that as one of my tables. But what I want is more info on the tasks themselves i.e. of the ones that are open - how many have been open for x amount of days.


The project already know that they have 27 open tasks - but now they want to know how long they have been waiting for them to be delivered.

 

[vijaySharma]

Hi Karen,


Do you have the flexibility to use another Helper column..


and use the Datediff function (hidden one)


=DATEDIF(Date1, Date2, Interval)

Where:

Date1 is the first date,

Date2 is the second date,

Interval is the interval type to return.


Set interval as d for days...


more help on Datediff here http://www.cpearson.com/excel/datedif.aspx


HTH


~VijaySharma

 

[karenlb]

I've come up with this in a helper column:

=IF(E6<>"",IF(E6<='2.2 Status'!$C$5,(DATEDIF(E6,'2.2 Status'!$C$5,"d")),0),0)


but that only tells me how long an issue has been open - i.e. it counts the number of days all cases have been opened.


How can I count only the ones where there is no closed date..?

 

[vijaySharma]

Do you have a column where you are storing the status....and a column where the closed date is stored.


You can then have a nested if to check the status and closure date and then count the days.


By default if the issue has not been closed you would use today's date to calculate the number of days it has been pending.... if there is an expected date of complete use that...


~VijaySharma

 

[karenlb]

Thanks. In the end I now have 4 helper columns!! Got there in the end.