Stacked Column to track task completion dates with conditions

**colbyclay** · 01-21-2010, 03:25 PM

Hello everyone (first post),
I am putting together a graph that will track tasks per person and show how many are open less than 15 days, between 15 and 30 days and over 30 days and how many have been closed by that person in less than 15 days, between 15 and 30 days and over 30 days.

Currently, there is a column titled "Date Issued", a column titled "PERSON" that the name of the responsable person will be entered in for each task, a column titled "Days open" that used the formula below to calculate the days the task was open until it was closed, and a column titled "closed date" where the date the task was completed is entered.

=IF([Date Issued]="","",IF([closed date]="",[Today()]-[Date Issued],[closed date]-[date issued]))

I have no idea how to set up a graph that will pull the names from "Person" column and stack the 3 different date conditions I want to see. If anyone has any suggestions, I would greatly appreciate it.

Thanks

**teylyn** · 01-21-2010, 03:52 PM

Hi Colbyclay,

welcome to the forum

Excel can only chart data that you prepare, so you need to create a table where the different categories are summed up per person. Then create a chart from that table.

If you upload some sample data it will be easier to suggest a solution.

**colbyclay** · 01-21-2010, 04:30 PM

Thanks Teylyn!

Well, that is beyond me as well. I have attached the data that I am collecting and trying to graph. I am thinking this would be an IF formula, but I dont know how to set it up.

What I would like is a graph that took the managers name and stacked the number of open task and categorized them as less that 15 days, 15-30 days and >30 days. Then next to it I would like a graph that shows closed tasks categorized the same way.

Also, as an aside, I am trying to figure out how to keep the blank cells under Days Open from turning read. I only want cells with data in them to turn colors. Any ideas where I went wrong?

Thanks

**teylyn** · 01-21-2010, 04:47 PM

See what you can do with the attached. I've used Countifs() to sum up the data in a table on Sheet2. Now you can graph off that. One suggestion included, but you can do a stacked column if you prefer.

Ultimately, a pivot table to sum up your data might also be an option.

Let me know if this works for you

**colbyclay** · 01-21-2010, 05:29 PM

I like it teylyn! Right now it doesn't take into account if the task is opened or closed. What would I need to add to the equation to get it to just look for open task? Once again, thanks for your help.

**teylyn** · 01-21-2010, 05:43 PM

Hi,

just change the formulas to include another parameter

C4 =COUNTIFS(Sheet1!B:B,$B4,Sheet1!C:C,"<15",Sheet1!D:D,"")
D4 =COUNTIFS(Sheet1!B:B,$B4,Sheet1!C:C,">=15",Sheet1!C:C,"<=30",Sheet1!D:D,"")
E4 =COUNTIFS(Sheet1!B:B,$B4,Sheet1!C:C,">=15",Sheet1!C:C,">30",Sheet1!D:D,"")

Also, as an aside, I am trying to figure out how to keep the blank cells under Days Open from turning read. I only want cells with data in them to turn colors. Any ideas where I went wrong?

Create another formatting rule, starting in C13. Use a formula to determine the format

=C13=""

Then set the background fill to none if condition is met. Set the range of the formatting to apply to =$C$13:$C$212

hth