Stuck on a Gantt Chart

taino8632

Board Regular
Joined
Nov 16, 2004
Messages
90
I've created a gantt chart using conditional formatting.
Book5
ABCDEFGHIJKLMNOPQ
2PhaseStartEnd%Complete03/18/0704/01/0704/15/0704/29/0705/13/0705/27/0706/10/0706/24/0707/08/0707/22/0708/05/0708/19/07
3
4Definition02/19/0707/27/0750%
5SystemSpec03/05/0708/03/0775%
6DetailDesign06/25/0709/14/0775%
7CodeandUnitTest08/17/0701/01/080%
8TestPrep08/17/0701/01/080%
9IntegrationTest12/03/0702/01/080%
10SystemTest06/15/070%
11PerformanceTest02/04/0803/28/080%
12ParallelTest06/25/070%
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.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,258
How about using a character from a different font - for example I have got some diamond shapes in the Wingdings font.
 

taino8632

Board Regular
Joined
Nov 16, 2004
Messages
90
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

Well-known Member
Joined
Mar 26, 2007
Messages
9,258
In MS Project, milestones are defined as being tasks with duration = 0.

You could do something like that.
 

taino8632

Board Regular
Joined
Nov 16, 2004
Messages
90
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

Well-known Member
Joined
Mar 26, 2007
Messages
9,258
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
Code:
=if($C2=$B2,u,"")
[code]
and format these new columns as Wingdings font.

This is a bit fiddly, I'll admit.
 

Forum statistics

Threads
1,181,412
Messages
5,929,782
Members
436,689
Latest member
IbraheemWard

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top