Stuck on a Gantt Chart

taino8632

Board Regular
Joined
Nov 16, 2004
Messages
90
Office Version
  1. 365
Platform
  1. Windows
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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
How about using a character from a different font - for example I have got some diamond shapes in the Wingdings font.
 
Upvote 0
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.
 
Upvote 0
In MS Project, milestones are defined as being tasks with duration = 0.

You could do something like that.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

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
Back
Top