+ Reply to Thread
Results 1 to 6 of 6

Stacked Column to track task completion dates with conditions

  1. #1
    Registered User
    Join Date
    01-21-2010
    Location
    San Antonio TX
    MS-Off Ver
    Excel 2007
    Posts
    14

    Stacked Column to track task completion dates with conditions

    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

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,363

    Re: Stacked Column to track task completion dates with conditions

    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.

  3. #3
    Registered User
    Join Date
    01-21-2010
    Location
    San Antonio TX
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Stacked Column to track task completion dates with conditions

    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
    Attached Files Attached Files
    Last edited by teylyn; 01-21-2010 at 04:57 PM. Reason: removed spurious quote

  4. #4
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,363

    Re: Stacked Column to track task completion dates with conditions

    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

  5. #5
    Registered User
    Join Date
    01-21-2010
    Location
    San Antonio TX
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Stacked Column to track task completion dates with conditions

    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.

  6. #6
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,363

    Re: Stacked Column to track task completion dates with conditions

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1