Changing color of the completed milestone bar on timeline chart

[SamirDutta]

Hi Chandoo

Although I have been using excel for a long time past, but truth is that I had not used the power of excel until I came across chandoo.org. Thanks a lot for your help. Here is my question for your guidance.


Following your tips I could create a project milestone on the timeline chart. Each milestone is represented by a bar on the chart. I want to show the completed milestone in a different color based on the boolean value in another column which has the value -"Pending" / "Completed". I want to show all completed milestone bar in Green, while pending ones in maroon color.


Thanks.


Samir

 

[SamirDutta]

Excel experts, waiting for your guidance to my problem. Will be thankful for your help.

 

[dan_l]

Use a stacked chart.


For giggles, if your values are


15

20

25


You'll make a second column with:

=if(a1<=20,a1,#n/a)


That will be your in process series


Your second column would be:

if(a2>20,a1,#n/a)


That's your completed series.


Then just apply the formatting you want.

 

[SamirDutta]

Thanks Dan for your response. But you have to excuse me as it seems I did not clearly explain my need. Let me try once again.


A milestone bar chart on the project timeline showing milestone event dates is already created. Let us assume, there are 8 milestone identified. At the start of the project the status column in data serie is showing 'Pending' and on the chart the milestone are shown in color Red. As the project timeline progresses and first milestone is crossed, the status is changed to 'Completed' indicating a crossed milestone. When the chart is refereshed for next client reporting. the milestone bar needs to be shown in Green to convey the message that particular milestone has been crossed/achieved/Completed.


My need is as to how this color change can be automated upon chart being refreshed in order to keep the chart sync with the data series. I am looking for tips to achieve in Excel 2007.


Thanks once again.


Samir

 

[fred]

I suppose you have your stages of project is from B1 to I1. The status of dates (actual and planned) on row 2 from B2 to I2.


Say B2 to D2 dates are actual and dates on E2 to I2 is pending/incomplete.


Next you need two more rows for the line bar chart.

Enter "completed" in A3. And row 3 formula from B3 copy across to I3 is:

=IF ( NOW ( ) -B2 > = 0, B2 ,0 )


Enter "Pending" in A4. Row 4 formula from B4 copy across to I4 is:

= IF( NOW ( ) - B2 < = 0, B2 , 0 )


Then make a line bar chart encompassing B1 to i1 and A3 to I4. You will see a line bar chart created for you.


Note: set your X-axis minimum a certain number that is less than the smallest date in the database.


The rest is just cosmetic work. if you are using 2007 like me, the completed bar would be in blue and the pending is in red.

 

[fred]

Or in the formula instead of using 0 when error, use "". Then you can overlap the bars 100% to look prettier on the chart.