Gantt chart formulas and conditional formatting

[BravoBravoAu]

Hey geniuses - grateful for any guidance on setting up a Gantt chart.

I've spent way too long looking for template Gantt charts and cannot work out how the formulas and conditional formatting work. I have been unable to find a suitable template that I could adapt. This was my first plan.

I have a worksheet that is basically divided in two parts:
- the left side is the details of tasks (task name, who its assigned to, its status (as a four option list), progress (as a percentage), start and finish dates).
- the right side was intended to show the progress of each task, simply by month from Dec 22 until Jun 24.

Issue 1: Initially I was thinking I could do this with conditional formatting, to fill the corresponding row for start and end month of each task. I have completed the start month, however then can't work out how to do the end month and then fill format what is between.
Issue 2: I was hoping to show percentage of progress as a change in fill relative to each task (for instance if a task was to take 4 months and was 50% completed, there would be a colour change shown for the first two months). I now doubt this is possible.
Issue 3: I was hoping to flag at risk projects with a 'tick-box' to then insert a shape within the chart. I now doubt this is possible.
Issue 4: Is there a better was to show allocated months, progress and completeness in a Gantt chart?

In short, please help. Any Gantt chart guidance would be appreciated before I lose my mind.
Cheers, BBau

 

[etaf]

i think most of those can be done
for example

here is part of an old gnatt chart i used as a template years ago
Where I also have milestones flagged

so it covers
point 1
NOT point 2 - but that maybe possible in a separate cell on its own showing % complete with a gradient CF
Point 3 - I have used milestone as a flag - sure that maybe changed to do what you need
Point 4 - not sure exactly what this means

There are also loads more gnatt type charts around, that i have used

anyway
what version of Excel - this example was based on 2010 version , so maybe much better ways now with 2021 or 365 and all the additional functions

Do you have a rough mockup of what you would like

Plan Gnant.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
4	Task	Description	Comment - Skills / People Required	Item Status	Start Date	Finish Date	Duration	17 Aug 15	24 Aug 15	31 Aug 15	07 Sep 15	14 Sep 15	21 Sep 15	28 Sep 15	05 Oct 15	12 Oct 15	19 Oct 15	26 Oct 15	02 Nov 15	09 Nov 15	16 Nov 15	23 Nov 15	30 Nov 15	07 Dec 15	14 Dec 15	21 Dec 15	28 Dec 15	04 Jan 16	11 Jan 16
5		Tender		Closed	8/20/15	9/12/15		1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
6	1	footings		Open	9/29/15	10/10/15		0	0	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0
7	1.1	foundations		Open	10/11/15	10/30/15		0	0	0	0	0	0	0	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0
8	1.2	inspection		Open	MILESTONE	11/2/15													M
Plan

Cell Formulas
Range	Formula
I4	I4	=MIN(F:F)-WEEKDAY(MIN(F:F),3)
J4:AD4	J4	=I4+7
I5:AD8	I5	=IF($F5="milestone", IF(AND(I$4<=$G5, J$4>$G5),"M",""), SUM((IF((I$4<$F5-MOD($F5-2,7)),0,IF((I$4>=$F5-MOD($F5-2,7)),1,0)))-(IF((I$4<$G5),0,IF((I$4>$G5),1,0)))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I5:AZ50	Expression	=AND(I5=1,$E5="closed")	text	YES
I4	Expression	=AND(TODAY()>=I4,TODAY()<J4)	text	YES
I4	Expression	=TODAY()>I4	text	YES
I5:AZ50	Expression	=AND(I5="M",$E5="open")	text	YES
I5:AZ50	Expression	=AND(I$4<TODAY(),I5=1)	text	YES
I5:AZ50	Cell Value	=1	text	YES
I5:AZ50	Cell Value	=0	text	YES
J4:AZ4	Expression	=AND(TODAY()>=J4,TODAY()<K4)	text	YES
J4:AZ4	Expression	=TODAY()>J4	text	YES

Cells with Data Validation
Cell	Allow	Criteria
E5:E8	List	Open, Closed

on share - I actually keep this on the share all the time, so unlike other examples i probably wont delete and will be on the forum for a while

Dropbox

www.dropbox.com

 

[etaf]

i have added a gradient in the duration column - But only for row 22
so H22 and H5 - so i can XL2BB

Point2
looks at the startdate and then today() and calculates the % that should be complete by today based on the start and end date
also shows the %

I think thats what you meant - as i say a sample mockup would help

EDIT - XL2BB doesnt show the gradient - CF - no idea why not - so have a link at bottom

Plan Gnant-v2.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
4	Task	Description	Comment - Skills / People Required	Item Status	Start Date	Finish Date	Duration	28 Sep 15	05 Oct 15	12 Oct 15	19 Oct 15	26 Oct 15	02 Nov 15	09 Nov 15
5		Tender		Closed	1/21/23	3/22/23	50.00%	0	0	0	0	0	0	0
Plan

Cell Formulas
Range	Formula
I4	I4	=MIN(F:F)-WEEKDAY(MIN(F:F),3)
J4:O4	J4	=I4+7
F5	F5	=TODAY()-30
G5	G5	=TODAY()+30
H5	H5	=(TODAY()-F5)/(G5-F5)
I5:O5	I5	=IF($F5="milestone", IF(AND(I$4<=$G5, J$4>$G5),"M",""), SUM((IF((I$4<$F5-MOD($F5-2,7)),0,IF((I$4>=$F5-MOD($F5-2,7)),1,0)))-(IF((I$4<$G5),0,IF((I$4>$G5),1,0)))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H5	Other Type	DataBar		NO
I5:AZ50	Expression	=AND(I5=1,$E5="closed")	text	YES
I4	Expression	=AND(TODAY()>=I4,TODAY()<J4)	text	YES
I4	Expression	=TODAY()>I4	text	YES
I5:AZ50	Expression	=AND(I5="M",$E5="open")	text	YES
I5:AZ50	Expression	=AND(I$4<TODAY(),I5=1)	text	YES
I5:AZ50	Cell Value	=1	text	YES
I5:AZ50	Cell Value	=0	text	YES
J4:AZ4	Expression	=AND(TODAY()>=J4,TODAY()<K4)	text	YES
J4:AZ4	Expression	=TODAY()>J4	text	YES

Cells with Data Validation
Cell	Allow	Criteria
E5:E49	List	Open, Closed

Dropbox

www.dropbox.com

 

[BravoBravoAu]

Thanks @etaf - I've managed to merge some of your great suggestions into my existing worksheet. Thank you. I'm using Excel 2016.

If anyone else has contributions - please chip in!

What I have left to resolve is the actual Gantt chart, which I can't seem to work out, even with your sample! Admittedly, I made it more complicated with the 'milestone' issue.

Column C is tasks (C5:C72 presently).
Column G is a Status List (Not Commenced, In Progress, Complete and On Hold).
Column J is Start Date.
Column M is Expected Completion date.
Column Q:AO are months (Dec 22 - Dec 24) - more than shown below..

I would like to colour fill the relevant rows of months between the 'start date' and 'Expected completion date'. I would like the colour of fill to be dependent on the status. For instance: Not Commenced = grey, In Progress = orange, Complete = green and On Hold = red).

Below is a simpler version with a small amount of relevant data.

	Column C	Column G	Column J	Column M	Column Q	Column R	Column S	Column T	Column U	Column V	Column W
Row 2	Tasks	Status	Start Date	Expected completion	Dec 22	Jan 23	Feb 23	Mar 23	Apr 23	May 23	Jun 23
Row 5	Draft Plan	Complete	1 Dec 22	28 Feb 23	Fill green	Fill green	Fill green
Row 6	Consult Plan	In Progress	1 Mar 23	1 May 23				Fill orange	Fill orange	Fill orange
Row 7	Finalise Plan	Not Commenced	1 May 23	14 May 23						Fill grey	Fill grey
Row 8	Board Approval	Not Commenced	1 Jun 23	1 June 23							Fill grey

Gee I hope this makes sense. Obviously When either 'start date' or 'expected completion' are changed, the fill needs to adjust. I have a sample workbook but I can't access XL2BB on this laptop to post it. Hopefully this is enough information for you!

 

[etaf]

Three rules setup in Conditional Formatting - 1 for each colour

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
$R$5:$X$8 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=AND((R$2>=$M5-(DAY($M5)-1)),R$2<=$Q5,$J5="Not Commenced")

Format [Number, Font, Border, Fill] - Fill Greu
choose the format you would like to apply when the condition is true
OK >> OK

Setup rule for each colour

Grey
=AND((R$2>=$M5-(DAY($M5)-1)),R$2<=$Q5,$J5="Not Commenced")
Orange
=AND((R$2>=$M5-(DAY($M5)-1)),R$2<=$Q5,$J5="in Progress")
Green
=AND((R$2>=$M5-(DAY($M5)-1)),R$2<=$Q5,$J5="complete")

Gantt Chart Monthly.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1
2			Tasks							Status			Start Date				Expected completion	Dec-22	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23
3
4
5			Draft Plan							Complete			12-Dec-22				28-Feb-23
6			Consult Plan							In Progress			18-Mar-23				1-May-23
7			Finalise Plan							Not Commenced			1-May-23				14-May-23
8			Board Approval							Not Commenced			12-Jun-23				18-Jun-23
9
10
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
R5:X10	Expression	=AND((R$2>=$M5-(DAY($M5)-1)),R$2<=$Q5,$J5="Not Commenced")	text	YES
R5:X8	Expression	=AND((R$2>=$M5-(DAY($M5)-1)),R$2<=$Q5,$J5="in Progress")	text	YES
R5:X8	Expression	=AND((R$2>=$M5-(DAY($M5)-1)),R$2<=$Q5,$J5="complete")	text	YES

On a share dropbox, I will keep this on the share as a monthly Gantt chart

Dropbox

www.dropbox.com

 

[BravoBravoAu]

Thanks again @etaf - your advice has been amazing!

I worked out why I was having so much trouble - the date in my header (R2:AO2) was coming from somewhere else as text! Quickly resolved once I realised.

 

[etaf]

you are welcome
glad its all resolved for you