Gantt Chart In Excel

[kins]

hi

I have a gantt chart in excel and i have the following dates colored

06/11/2017 - 14/11/2017. The duration of the project is 9 weekday so I want the cells to color weekday. My start date and end date to the left is fine and excludes weekend (because I've put a weekday formula in) but I don't know how to get the actual chart color to change.

Any ideas?

 

[Perpa]

kins,
You didn't say what version of Excel you were using. I have Excel 2007 and this is how I change the bar color:

Goto your Gantt Chart, right click on one of the bars (that should select all bars of similar color).
From the drop down, select 'Format Data Series'. Select 'Fill', then 'Solid Fill', then select the color you want that bar to be. Then close the window and select a cell outside of the Gannt Chart. Done.

It may be slightly different depending on what version of Excel you are running, but perhaps this will take you in the direction you want to go.
Perpa

 

[kins]

kins,
You didn't say what version of Excel you were using. I have Excel 2007 and this is how I change the bar color:

Goto your Gantt Chart, right click on one of the bars (that should select all bars of similar color).
From the drop down, select 'Format Data Series'. Select 'Fill', then 'Solid Fill', then select the color you want that bar to be. Then close the window and select a cell outside of the Gannt Chart. Done.

It may be slightly different depending on what version of Excel you are running, but perhaps this will take you in the direction you want to go.
Perpa

thanks for the reply. when i highlight the bar i don't have the option for 'format data series'?

 

[Perpa]

thanks for the reply. when i highlight the bar i don't have the option for 'format data series'?

kins,
The information I shared was for a horiontal 'Stacked Bar Graph'. I should have asked how you created you Gantt Chart in the beginning. If you would like more information on this type of chart let me know and I will forward the instructions on how to create a Gantt Chart using this method.

If not, then you will probably want to make a new post describing the type Gantt Chart you have and include screen shots.
Sorry for the confusion.
Perpa

 

[kins]

kins,
The information I shared was for a horiontal 'Stacked Bar Graph'. I should have asked how you created you Gantt Chart in the beginning. If you would like more information on this type of chart let me know and I will forward the instructions on how to create a Gantt Chart using this method.

If not, then you will probably want to make a new post describing the type Gantt Chart you have and include screen shots.
Sorry for the confusion.
Perpa

thanks Perpa. Apologies for the confusion. The spreadsheet is something I have inherited and been asked to amended - i have no real experience with these. It is a project planner (in Exel).

To reiterate my issue. I have columns with dates and duration, I have put a workdays formula to calculate an end date that is only a week day but the corresponding bar (to the right) is selecting weekends. For example, I have plan start date of 06/11/2017 which is a duration of 9 day, the end date is 17/11/2017 oin the column but the corresponding bar is showing from 06/11/2017 - 14/11/2017. I hope this helps. Thanks for the advise, I appreciate it

 

[Perpa]

kins,

I am not familiar with the 'Excel planner' you describe. Perhaps you can show a screen shot of a sample of the data used to create the Gantt Chart, and a screen shot of the Gantt Chart itself. Those might serve to give us some information about what it is you have inherited. If you right click on the chart does a drop down of any kind open?

I'll watch this thread for a while to see if I can be of any help.
Perpa

 

[kins]

 

[kins]

Morning Perpa

Please see attached images. One shows how the wekeend of the 12th and 13th November is being highlighted in the bar – you can see that the end date takes out any weekends, because I’ve put the weekdays formula in there, but the bar isn’t recognising this. The other shows the data information within the ‘Name Manager’. In addition, the other shows the information contained within the conditional formatting tab – which I suspect is where I need to edit.

Re right clicking on the bar itself – nothing that would allow me to edit comes up.

Thanks for your help with this, I appreciate it.

 

[Perpa]

kins,
I think you are correct, you probably need to modify the rules to skip weekends, holidays, and other scheduled days off. Holidays, and other scheduled days off would require a list in two columns, column 1 with the date, and column 2 showing the Holiday/name. Weekends are known, so you just need the date for Friday of each week of the duration to check if the duration extends beyond Friday of each week.

The Project Planner appears well laid out. However, from what you have shown in the last figure 'Conditional Formatting Rules Manager', you may want to select 'T5', which is the selection of current interest, and see what those rules are. You may have to select 'AA5' and 'AB5' to see what rules are being applied for the weekend. From this side it would be helpful to see 'ALL' the rules for a selection, otherwise we don't have the complete picture. With that information we can have another look.
Perpa

 

[kins]

kins,
I think you are correct, you probably need to modify the rules to skip weekends, holidays, and other scheduled days off. Holidays, and other scheduled days off would require a list in two columns, column 1 with the date, and column 2 showing the Holiday/name. Weekends are known, so you just need the date for Friday of each week of the duration to check if the duration extends beyond Friday of each week.

The Project Planner appears well laid out. However, from what you have shown in the last figure 'Conditional Formatting Rules Manager', you may want to select 'T5', which is the selection of current interest, and see what those rules are. You may have to select 'AA5' and 'AB5' to see what rules are being applied for the weekend. From this side it would be helpful to see 'ALL' the rules for a selection, otherwise we don't have the complete picture. With that information we can have another look.
Perpa

Hello Perpa

As per request, please see attached remainder of the conditional formating. I'm going to look at the informaiton that you have just given me and try to apply it.

Thanks again for your help.