GANTT (horizontal bar chart) in excel

[decksie]

Hi

I'm trying to create a GANTT style chart (no float required). I've used a stacked bar chart, which has come close. That activity duration is in orange, but the bar starts from the lower bound of my horizontal axis (i.e I don't want the blue). How can I remedy this?

Is there a better way to do a GANTT?

Regards

 

[etaf]

I have used conditional formatting for Gnatt charts - rather than a graph
Did you specifically need a graph ?

heres an old example from 2014

Gnatt_Chart_Conditional_Formatting.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	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN
1	Activity Name	start	Duration		Finish	2/1/14	2/2/14	2/3/14	2/4/14	2/5/14	2/6/14	2/7/14	2/8/14	2/9/14	2/10/14	2/11/14	2/12/14	2/13/14	2/14/14	2/15/14	2/16/14	2/17/14	2/18/14	2/19/14	2/20/14	2/21/14	2/22/14	2/23/14	2/24/14	2/25/14	2/26/14	2/27/14	2/28/14	3/1/14	3/2/14	3/3/14	3/4/14	3/5/14	3/6/14	3/7/14
2	Complete Misc. - Poplar Tent (Stage 2)	18-Feb-14	45		4-Apr-14
3	Superstructure - Rocky River (Northbound)	18-Feb-14	13		3-Mar-14
4	Drainage (Seg. 1, Ph 2)	18-Feb-14	20		10-Mar-14
5	Complete Misc. - Rocky River (Northbound)	18-Feb-14	16		6-Mar-14
6	Superstructure - Coddle Creek (Northbound)	18-Feb-14	12		2-Mar-14
7	Drainage (Seg. 2, Ph 2)	18-Feb-14	36		26-Mar-14
8	Drainage (Seg. 2, Ph 3)	18-Feb-14	7		25-Feb-14
9	Removal of Temp Structure	20-Feb-14	14		6-Mar-14
10	Anchor Barrier Wall ( 22 Pieces) (Punchlist Item)	19-Feb-14	30		21-Mar-14
11	Drainage (Seg. 3, Ph 3)	21-Feb-14	45		7-Apr-14
12
Sheet1

Cell Formulas
Range	Formula
E2:E11	E2	=SUM(B2+C2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:CO11	Expression	=AND(F$1>=$B2,F$1<=$E2)	text	NO

I have others I think with Milestones etc
I'm out all day today - i do have others,
would this type of thing work for you

maybe post your data here using XL2BB add-in - see signature / menu
OR
post on a share like dropbox/onedrive/google


heres the example with milestones

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	AE	AF	AG	AH	AI
2
3								week commencing
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	18 Jan 16	25 Jan 16	01 Feb 16	08 Feb 16	15 Feb 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	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	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	0	0	0	0	0
8	1.2	inspection		Open	MILESTONE	11/2/15													M
9	2	brick laying		Open	11/5/15	1/27/16		0	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1	1	1	0	0	0
10	3	roofing Frame		Closed	12/19/15	1/28/16		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	0	0	0
11	3.1	roof slate/tiling		Open	1/28/16	1/29/16		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0
12	3.2	inspection		Open	MILESTONE	1/30/16																									M
13	4	Electrical		Open	12/6/15	1/31/16		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	0	0	0
Plan

Cell Formulas
Range	Formula
I4	I4	=MIN(F:F)-WEEKDAY(MIN(F:F),3)
J4:AI4	J4	=I4+7
I5:AI13	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)))))
F10	F10	=G10-40
F13	F13	=G13-56

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:E13	List	Open, Closed

 

[decksie]

Thanks Etaf! I'll have a play with these and see how I go.

I see from your example you noticed the civil engineering data! I'm pulling in dates from P6 excel dumps using programme codes. I'm trying to be able to compare different programmes and how the elements move while also being able to switch between programmes. I had that much working ..... so I need to think about how I would adapt to your idea which would have the benefit of being able to use the condition formatting to distinguish different programmes, elements. But yeah the programme I'm reading from, generated in P6 and exported to excel are over 13000 rows long, so I'm trying to automate as far as possible.

 

[etaf]

you are welcome, come back here if you need any further help/clarification or alternative charts