Stuck on a Gantt Chart

[taino8632]

I've created a gantt chart using conditional formatting.

Book5
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	Phase	Start	End	%Complete		03/18/07	04/01/07	04/15/07	04/29/07	05/13/07	05/27/07	06/10/07	06/24/07	07/08/07	07/22/07	08/05/07	08/19/07
3
4	Definition	02/19/07	07/27/07	50%
5	SystemSpec	03/05/07	08/03/07	75%
6	DetailDesign	06/25/07	09/14/07	75%
7	CodeandUnitTest	08/17/07	01/01/08	0%
8	TestPrep	08/17/07	01/01/08	0%
9	IntegrationTest	12/03/07	02/01/08	0%
10	SystemTest		06/15/07	0%
11	PerformanceTest	02/04/08	03/28/08	0%
12	ParallelTest		06/25/07	0%
Gantt (2)

The formulas are as follows:

Condition 1: AND(I$2>=$C4,I$2<=$C4+($D4-$C4+1)*$E4)
Condition 2: AND(I$2>$C6+($D6-$C6+1)*$E6,I$2<=$D6)
Condition 3: OR(I$2=$B$1,I$2=$B$1+7)

The gantt shows bi-weekly dates. Condition 1 gives me the overall duration of the task, Condition 2 creates the completed duration, Condition 3 places a red vertical line on the current week.

I am stuck on how to create milestones. Some of our tasks are milestone dates and I would like to highlight them using a diamond or something similar.

All help is appreciated.

 

[Gerald Higgins]

How about using a character from a different font - for example I have got some diamond shapes in the Wingdings font.

 

[taino8632]

How about using a character from a different font - for example I have got some diamond shapes in the Wingdings font.

I would love to use windings for my diamond shape. However, how do I get Excel to know that the task is a milestone and not a regular task?

Otherwise, my chart will have a horizontal bar where I only want the diamond for the milestone.

 

[Gerald Higgins]

In MS Project, milestones are defined as being tasks with duration = 0.

You could do something like that.

 

[taino8632]

In MS Project, milestones are defined as being tasks with duration = 0.

You could do something like that.

I believe you're suggesting that I add a duration column, correct? If so, any ideas on how to modify/replace my formula(s) to accomodate the new data?

 

[Gerald Higgins]

To be honest I think you might be better off getting hold of MS Project.

Anyway, the duration calculation would simply be the entries in Col C minus those in Col B.
You could put in additional narrow columns between the weekly columns, and use these new columns to show your milestones.

For example add a new column between your existing Cols F and G, and insert this formula

=if($C2=$B2,u,"")
[code]
and format these new columns as Wingdings font.

This is a bit fiddly, I'll admit.