Creating a Gant Bar in a row for a task

[Robc]

Howdy!
Can anyone tell me how to create a Gant Chart bar effect by shading
cells on the same row as a task with a duration and a start and end
date? I am a cheapskate and do not want to buy MS Project to do this
very simple task, if I don't have to. Plus I don't need all the bells
and whistles of MS Project for this.

The sheet I have created looks similar to a MS Project Gant Chart view
with each row representing a task and each row has the following
cells(columns) Task ID, Task Description, Duration, Start date and End
date, then I have 52 columns representing weeks. I have a lot of
projects with the same tasks (about 10) for each project. Obviously
this does not have to be extrmely accurate ( meaning down to the day) I
just simply want to view all the task durations for the year for each
project together in one excel file. However, I want to automate the
bar creation based on the duration value and the start date. So by
changing either the start date or the duration the bar graph for that
row would reflect accordingly.

Any help would be appreciated!

Thanks,
Rob

 

[Debra Dalgleish]

Assuming cells F1 to BE1 contain the numbers 1 to 52, enter the
following formula in cell F2 (this requires that the Analysis Toolpak be
installed):

=IF(AND(WEEKNUM($D2)<=F$1,WEEKNUM($E2)>=F$1),"X","")

Copy the formula across to week 52, and down to the last task.

Then, use Conditional Formatting to shade the cells:
Select all the cells that contain the above formula
Choose Format>Conditional Formatting
Leave the first dropdown as Cell Value Is
From the next dropdown, choose 'equals'
In the text box, type an X
Click the Format button, and on the Pattern tab, choose a colour
Click OK, click OK

Format the cells' font colour to match the shading.