WBS and Gantt Chart - Formatting

[mhb757]

I'm creating a WBS structure and associated Gantt chart within Excel to assist in the planning and execution of a project. My workbook as it stands has three tabs - WBS legend, WBS, and Gantt chart.

I've defined the WBS structure with a number structure as follows:

[TABLE="width: 237"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Task Type[/TD]
[TD]WBS[/TD]
[/TR]
[TR]
[TD]Project Area[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Phase[/TD]
[TD]1.1[/TD]
[/TR]
[TR]
[TD]Subtask[/TD]
[TD]1.1.1[/TD]
[/TR]
[TR]
[TD]Deliverable[/TD]
[TD]1.1.1.1[/TD]
[/TR]
</tbody>[/TABLE]

There are multiple project areas, phases, subtasks, and deliverables, and the structure would add a number for each new iteration. In other words, the second phase within project area 1 would be 1.2, or the second project area would start at 2.

The WBS tab has fields WBS, Project Area, Task Type, and Task Description, along with reviewers and team members. In addition, I've added a Start Date and End Date to calculate the duration by each task. I translated the WBS into a Gantt chart by modifying a Stacked Bar chart. Although, I haven't had issues with translating the data into the chart, the formatting of the WBS and charts has been more of the issue.

I want to see if there is a way to adjust the formatting of the WBS based on values in the WBS legend tab. For example, the legend defines the colors - project area 1 are varying shades of blue for each task type, project area 2 are varying shades of green, etc. I would like to carry that color structure to the WBS, and over to the Gantt chart.

I could do this manually, but given this chart is going through various edits and reviews, I want to ensure a maximum level of automation and efficiency. I've proposed we use MS Project, but there's push back because not everyone within the organization has the program, nor understands the functionality.

Please let me know if I may clarifying anything further, or provide any backup data. Thank you!

 

[RickXL]

Hi,

If it was me, I would not use a Chart to display the Gantt. I don't know what temporal resolution you require but, assuming it is daily, then use one column per day. All the WBS stuff can appear in the columns on the left hand side and the bars will result from colouring in the cells. You could use VBA or Conditional Formatting to do that.

Assuming Conditional Formatting, one way would be to assign a date to each column. The test for a cell's colour would be to see if the date in the header row for that activity was between the start and finish dates in the left hand columns. Note: The dates do not need to be readable. They are just for the Conditional Formatting to work.

Another way would be to use a chart start date then just number the columns. You would get the date for the comparison by adding the number to the start date. That approach would make it easier to change the chart's start date.

Here is an example:

Excel 2013
	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
1	Start Date	02/09/2016
2						Sep 04							Sep 11							Sep 18							Sep 25							Oct 02
3	WBS	Start	Finish		1	2	3	4	5	6	7	8	9	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##	##
4	1	01/08/2016	01/11/2016
5	1.1	11/09/2016	01/11/2016
6	1.1.1	18/09/2016	30/09/2016
7	1.1.2	24/09/2016	17/10/2016
8	1.2	09/10/2016	02/11/2016
9	2	22/10/2016	09/11/2016
10	2.1	29/09/2016	09/10/2016
11	2.1.1	05/11/2016	26/11/2016
12	2.1.2	17/12/2016	12/01/2017
Sheet1

Cell Formulas
Range	Formula
E2	=IF(WEEKDAY($B$1+E$3)=1,$B$1+E$3,"")
F2	=IF(WEEKDAY($B$1+F$3)=1,$B$1+F$3,"")
G2	=IF(WEEKDAY($B$1+G$3)=1,$B$1+G$3,"")
H2	=IF(WEEKDAY($B$1+H$3)=1,$B$1+H$3,"")
I2	=IF(WEEKDAY($B$1+I$3)=1,$B$1+I$3,"")
J2	=IF(WEEKDAY($B$1+J$3)=1,$B$1+J$3,"")
K2	=IF(WEEKDAY($B$1+K$3)=1,$B$1+K$3,"")
L2	=IF(WEEKDAY($B$1+L$3)=1,$B$1+L$3,"")
M2	=IF(WEEKDAY($B$1+M$3)=1,$B$1+M$3,"")
N2	=IF(WEEKDAY($B$1+N$3)=1,$B$1+N$3,"")
O2	=IF(WEEKDAY($B$1+O$3)=1,$B$1+O$3,"")
P2	=IF(WEEKDAY($B$1+P$3)=1,$B$1+P$3,"")
Q2	=IF(WEEKDAY($B$1+Q$3)=1,$B$1+Q$3,"")
R2	=IF(WEEKDAY($B$1+R$3)=1,$B$1+R$3,"")
S2	=IF(WEEKDAY($B$1+S$3)=1,$B$1+S$3,"")
T2	=IF(WEEKDAY($B$1+T$3)=1,$B$1+T$3,"")
U2	=IF(WEEKDAY($B$1+U$3)=1,$B$1+U$3,"")
V2	=IF(WEEKDAY($B$1+V$3)=1,$B$1+V$3,"")
W2	=IF(WEEKDAY($B$1+W$3)=1,$B$1+W$3,"")
X2	=IF(WEEKDAY($B$1+X$3)=1,$B$1+X$3,"")
Y2	=IF(WEEKDAY($B$1+Y$3)=1,$B$1+Y$3,"")
Z2	=IF(WEEKDAY($B$1+Z$3)=1,$B$1+Z$3,"")
AA2	=IF(WEEKDAY($B$1+AA$3)=1,$B$1+AA$3,"")
AB2	=IF(WEEKDAY($B$1+AB$3)=1,$B$1+AB$3,"")
AC2	=IF(WEEKDAY($B$1+AC$3)=1,$B$1+AC$3,"")
AD2	=IF(WEEKDAY($B$1+AD$3)=1,$B$1+AD$3,"")
AE2	=IF(WEEKDAY($B$1+AE$3)=1,$B$1+AE$3,"")
AF2	=IF(WEEKDAY($B$1+AF$3)=1,$B$1+AF$3,"")
AG2	=IF(WEEKDAY($B$1+AG$3)=1,$B$1+AG$3,"")
AH2	=IF(WEEKDAY($B$1+AH$3)=1,$B$1+AH$3,"")

The Conditional Formatting formula is: =AND(($B$1+E$3)>=$B4,($B$1+E$3)<=$C4)
The formu;la for the dates is dynamic. It displays Sundays and responds to Start Date changes. The text has been formatted and rotated 90 degrees.
The day numbers have been hidden (on the real worksheet) by formatting the bar and the text the same colours.


regards,