Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Mar 2008
    Location
    Port Talbot, South Glamorgan, United Kingdom
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create project plan (2007)

    Hi

    The attached file has class Courses along with Start and End dates.

    I manually created a project plan overview from the specified start date to the end.

    Can Excel help me create such a project chart automatically from the dates provided?

    This would be less error-prone to my doing it manually like this.

    Thanks.

    Alan
    Attached Files Attached Files

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create project plan (2007)

    <P ID="edit" class=small>(Edited by wdwells on 19-Jan-09 14:16. Reformatted workshet)</P>The attached file provides one solution using conditional formatting.

    Note:
    <UL><LI>The columns have been changed to weeks instead of months
    <LI>The columns are identified by the Monday
    <LI>Courses should be identified by the Monday they start and the Friday they finish[/list]HTH
    Attached Files Attached Files
    Regards
    Don

  3. #3
    Lounger
    Join Date
    Mar 2008
    Location
    Port Talbot, South Glamorgan, United Kingdom
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create project plan (2007)

    Thank you very much; this is really interesting.

    I tried to recreate what you've done, step-by-step. But the green shading doesn't appear.

    Please can you help as it seems I'm just 1 step from completion.

    Alan
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create project plan (2007)

    It's not clear to me what Don had in mind - the green shading in his workbook has been applied directly, not through conditional formatting. See the attached workbook. Click in any cell in the 'project plan' and select Format | Conditional Formatting...
    Attached Files Attached Files

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create project plan (2007)

    Hi Alan
    As Hans said, The green shading is applied directly, and the conditional formatting applies white shading if the start date precedes that week or the end date falls after it.
    Regards
    Don

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Create project plan (2007)

    Another way is to use a bar chart which has more resolution in the start and end dates.

    Steve
    Attached Files Attached Files

  7. #7
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create project plan (2007)

    Hi Steve

    I believe that it is possible to control the X axis of a scatter graph such that the time divisions are precisely in line with the calendar month. Are you aware of any technique to achieve this with a bar graph.
    Regards
    Don

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Create project plan (2007)

    You can do it on the X-axis for many charts directly since the x-Axis can be category, time-scale, or values. The Y- Axis is always values so it can not be done directly.

    Indirectly it can be by hiding the Y- Axis and using a custom Axis. MS MVP Jon Peltier has an Arbitrary Axis Scale example which can be adapted.

    It gets cumbersome to "draw" all the objects on an XY scatter and if filling is desired it adds another level of complexity.

    Steve

  9. #9
    Lounger
    Join Date
    Mar 2008
    Location
    Port Talbot, South Glamorgan, United Kingdom
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create project plan (2007)

    Thank you all for your great help.

    Attached is the file now with an extra field showing percentage completion.

    Based upon that, is it possible to have a line within each graph whose length will graphically show the progress?

    Would be good if the line end can have the percentage as well.

    Thanks.

    AS
    Attached Files Attached Files

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create project plan (2007)

    Not exactly what you asked because you're working with cells, not with a chart, but I've added some intermediate formulas and a second format condition so that the "finished" part has a different color.
    Attached Files Attached Files

  11. #11
    Lounger
    Join Date
    Mar 2008
    Location
    Port Talbot, South Glamorgan, United Kingdom
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create project plan (2007)

    Thanks Hans, it'll take me a few days to go thru this and understand how it works.

    -Alan

Posting Permissions

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