Gantt Chart & Formulas

[Darren Guess]

Good Afternoon All

I currently have a Gantt chart within an excel report that I have managed to get working based upon my limited knowledge and searching threads on this forum. Although it works ok there are elements that I would like to work a little better. The layout of the gantt data is displayed below where:</SPAN>

1) Any row with a purple task in column B has the start and end dates in columns C & E fed from another sheet within the excel. The entire row is locked for editing by any user other than myself, as these are used for performance measurement.
2) All other rows (with a yellow task in column B) have the start and end dates entered by a defined team for each report, where additional rows can be added by copying the last row (38) and inserting where required (As these already have the formulas in the respective cells).
3) The chart is then displayed on a seperate tab where there are currently 4 data series used for the chart. The 'Start Date' (column C) data series is filled with no colour, so that the Gantt appears floating from it's respective start date. Days Completed (column F) displays in green as the chart is advanced along. Days left to complete (column G) displays in blue and finally Additional Days (Column H) displays in red.

If we take row 8 below as an example to explain the formulas these are as follows:
D8 (Duration Days) =E8-C8.
F8 (Days Completed) =IF(J8>=I8,I8,J8).
G8 (Days Left to Complete) =IF(I8-F8<0,0,I8-F8).
I8 (Total Days to Complete) = E8-C8.
J8 (Non-Negativity) =IF($C$43>=C8,$C$43-C8,0) where C43 is a date fed in from another sheet.

Excel 2007

	B	C	D	E	F	G	H	I	J
2	Note: When using the Gantt Chart Input Data, only cells & dates that are coloured in green require filling out. All other dates will be calculated automatically Please note that the task description cells highlighted in Purple should not be renamed
3
4
5
6	Check Duration	63
7	Task	Start Date	Duration (days)	End Date	Days Completed	Days left to complete	Additional Days	Total days to complete	Non-negativity
8	Pre Dock	01/08/12	1	02/08/12	1	0		1	50
9	Functions	01/08/12 00:00	3	04/08/12 00:00	3	0		3	50
10	Thrust Reverser Functions	01/08/12 00:00	3	04/08/12 00:00	3	0		3	50
11	FR 80 Doubler corrosion investigation	01/08/12 12:00	2	03/08/12 13:00	2	0		2	50
12	FR 80 Data sent to AIB	03/08/12 13:00	0	03/08/12 15:00	0	0		0	47
13	Awaiting AIB FR 80 Response	03/08/12 15:00	2	06/08/12 00:00	2	0	3	2	47
14	FR 80 & FR 58 Stress jacking	09/08/12 07:00	1	09/08/12 19:00	1	0		1	42
15	FR 80 Repair assessment	09/08/12 13:00	2	11/08/12 19:00	2	0		2	41
16	Open Up	01/08/12 00:00	8	09/08/12 00:00	8	0		8	50
17	Base Insp	02/08/12 00:00	12	13/08/12 13:00	12	0		12	49
18	Supplemental Insp	02/08/12 00:00	24	25/08/12 19:00	24	0		24	49
19	ETIC Review	26/08/12 00:00	1	27/08/12 00:00	1	0		1	25
20	Ageing A/C Review	02/08/12 00:00	15	17/08/12 00:00	15	0		15	49
21	NDT Programme	07/08/12 00:00	18	25/08/12 00:00	18	0		18	44
22	Routine Maintenance	09/08/12 00:00	42	20/09/12 00:00	42	0		42	42
23	Defect Rectifications	09/08/12 00:00	49	27/09/12 00:00	42	7		49	42
24	Close up	01/09/12 00:00	26	27/09/12 00:00	19	7		26	19
25	Power on	03/09/12 00:00	1	04/09/12 00:00	1	0		1	17
26	FR47 AIB Response	15/09/12 00:00	1	16/09/12 00:00	1	0		1	5
27	FR47 Repair Action ?	15/09/12 00:00	7	22/09/12 00:00	5	2		7	5
28	Frame 48+51 installation	19/09/12 00:00	3	22/09/12 00:00	1	2		3	1
29	Post Maintenance Functions	11/09/12 00:00	18	29/09/12 00:00	9	9		18	9
30	Post Dock	28/09/12 00:00	1	29/09/12 00:00	0	1		1	0
31	Engine Runs	28/09/12 00:00	1	29/09/12 00:00	0	1		1	0
32	Defuel and Sump	28/09/12 00:00	1	29/09/12 00:00	0	1		1	0
33	Weigh+Check Completion	29/09/12 00:00	1	30/09/12 00:00	0	1		1	0
34	Test Flight Programme	30/09/12 00:00	3	03/10/12 00:00	0	3		3	0
35	ETIC (Delivery)	02/10/12	1	03/10/12	0	1		1	0
36
37
38	Description		0		0	0		0	41172
39
40	To add lines, copy the above row, insert at the point you want it and manually set the highlighted dates. If you subsequently wish to add an additional line copy the above row once again and insert it at the point you want it, manually setting the highlighted dates.
41

<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Gantt Chart Data​

At present everything works ok and is fairly robust, but the one thing I would like to be able to do is to change the way column H (Additional Days) works. At the moment this is a simple data series on the chart, whereby if a user enters a number the series length is displayed in red. Every day the milestone is missed the user must remember to increase the value by 1 to advance the series on one more day. What I would like to do is to change the logic of this column, so that a user can enter the revised target date for that milestone. I cannot work out what formula would be required so that if the original End Date in column E is surpassed and a date is entered in column H the gantt will still advance as it does presently with a master date reference. I would like the extra days in the revised date series to display in say orange (to show how much we are potentially going to miss a target by), until the date has passed, when it would then turn red (to show how many days we actually missed the target).

I am hoping I have explained what I am looking to achieve well enough, as I have tried to insert the chart also for reference, without success. If someone can guide me how to do this I will post this up also.

Many Thanks in advance
Darren