Results 1 to 13 of 13
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    timeline with conditional formatting (Excel 2003)

    Oh hail yee great Conditional Formatting wizards.... <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

    Attached is a sample data set of what I am looking for and a partially completed formatted output. I want to show visually (not in a chart, but in a table) when a given 'thing' (actually a service) will start to be built and how long it'll take to implement. So if ServiceA is schedule to begin its building stage at Year3 and it will take 5 years to complete, I want a colored bar starting at Year3 and ending at Year7 (five years total). I have Year0 defined as starting Now.

    I am having problems coloring the starting year cell AND coloring the 'years to complete' cells with conditional formatting. The attached table has the formatting for the 'starting year' but the second part, yrs to complete, is colored by me manually. I want to make this such that when any of the year values change, this table changes too.

    How can I do this?

    Thnx,
    Deb

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

    Re: timeline with conditional formatting (Excel 2003)

    Does the attached do what you want? I modified the cell formulas, and based conditional formatting on the cell values.

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: timeline with conditional formatting (Excel 2003)

    WOW that was quick!! I barely left my desk to hit the bathroom and came back and you had the answer!! <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

    Thank you so much, this will make a great addition to my Excel app (which tracks migrating customers from old services to new and all the hassles of de-commissioning old services, costs, etc. and bringing up the new ones for new and existing customers).

    Deb

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: timeline with conditional formatting (Excel 2003)

    Can I ask one more favor.... The sample I included had a fixed set of services (the left side of the table with the names). In reality there are 1-7 services and so I'd like to format the table to show/hide or somehow format the table to show that some services (rows in the table) are not defined. I tried adding a 3rd conditional formatting (to cover the cells white on white to hide the data) but it didn't work.
    <pre>ServiceA X---------------------
    ServiceB X-------
    ServiceC X-----------------------
    None X--------
    None X------------</pre>

    This represents the table you helped me with where "X" is the start year and "------" is the duration. So in this case the service names with "None" have not been defined. Even if these services are not defined, there is still a default value for the StartYear and DurationPeriod so that's why the table is filled in for even services that do not exist.

    Any ideas?

    Thnx,
    Deb

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

    Re: timeline with conditional formatting (Excel 2003)

    In cell D10 in the demo workbook, enter this formula:
    <code>
    =IF($A10="None","",IF($C10=D$7,"X",IF($C10>D$7,"", IF($C10+$B10>D$7,"0",""))))
    </code>
    Fill down and right as far as needed.

  6. #6
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: timeline with conditional formatting (Excel 2003)

    Perfect! I was trying to add a 3rd formula to the conditonal formatting to test if that field = "None" and then color it white on white, but it didn't work.

    Thnx,
    Deb <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  7. #7
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: timeline with conditional formatting (Excel 2003)

    Here is a link that I have found to be very useful in developing Gantt charts (time lines) using conditional formatting.

    Gantt Chart

  8. #8
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: timeline with conditional formatting (Excel 2003)

    Excellent!! I'll tweak my current version based on this article. Very nice <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>

    Thank you,
    Deb

  9. #9
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Seattle, Washington, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: timeline with conditional formatting (Excel 20

    Do the Excel files attached to this thread still exist somewhere? I need the same help and would love to see them.

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

    Re: timeline with conditional formatting (Excel 20

    They were lost in the server crash in August, unfortunately, and I didn't keep a copy.
    Perhaps Jujuraf still has her version.

    However, the link in Don Sadler's reply is still valid, and the article it refers to describes essentially the same method as the one I used.

  11. #11
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Seattle, Washington, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: timeline with conditional formatting (Excel 20

    OK, that link looks helpful. Thanks!

  12. #12
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: timeline with conditional formatting (Excel 20

    Yes I remember this one, it really helped me on a project I was doing. I found the sample I had sent which was then edited with the solution. At the same time I also found another one which is even more Gantt-like than mine and much more sophisticated.

    File 1 (attached). Takes a start period and duration and colors the cells (1 cell = 1 time period, in my case 1 year). The start year is blue, the 'duration' years are orange.
    File 2. Sophisticated Gantt chart sample template. Download from http://www.vertex42.com

    Deb
    Attached Files Attached Files

  13. #13
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Seattle, Washington, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: timeline with conditional formatting (Excel 20

    Your attached file looks like exactly what I needed. Thank you!

Posting Permissions

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