Today's lesson explains how to implement a dynamic Gantt Chart using Google Spreadsheets and conditional formatting (change with rules option).
Take a look at this spreadsheet or copy this spreadsheet in your Google Apps AccountImplement the gantt chart
For explain the topic of this post I prepared a spreadsheet whit a simple work breakdown structure, with only four columns (WBS, activity, start date, finish date).
The question is: how do you do to change a date and update automatically the gantt chart?
You can think to solve the problem in this way, using a simple IF statement: for each activity, if the date's value, contained into the row 2 (in gray), is equal or grater than the activity's start date or the same date's value (row 2) is equal or less then the activity's finish date, add an "X" into the cell. Otherwise leave the cell blank. Formula for the cell C4 is:
=if(AND((E$2>=$C4),(E$2<=$D4)),"X","")
You can copy and paste this formula into the other cells.
So, if a cell satisfies the previous rule, you can use conditional formatting, to change the cell's background from the default color (white) to another color.
Click on cell background color icon and select Change with rules.
Select the following rules:
Text is exactly = X
... then, select the background color you want to apply to the cell.
How you can see in the first image, I used two color: yellow for activities and green for activities'tasks. You can repeat the previous rule changing the color from yellow to green for your tasks.
Take a look at this spreadsheet here.
Similar posts
I am 
Can we get a working version? I can only open it read only.
Thank you for your effort, looks great...
Marc
May I suggest looking at www.gganttic.com/Demo.aspx
It is a true gantt chart with interactive edtiting which is connected to google spreadsheet as a back end. One downside - you shall have to install Silverlight to use it.
Hi Antonio, thanks for sample.
I've enhanced your spreadsheet with:
- start date of planner is today
- show weekends
- auto show month number
I've published an example here:
http://spreadsheets.google.com/pub?key=pyYwtfUcwRJV4Jofd61AgMw
This formula works for week-by-week rather than day-by-day:
=if(OR((AND((G$2<$C3),(G$2+7<$D3))),(AND((G$2+7>$C3),(G$2>$D3)))),""," ")
Conditional formatting: if there's a space, color that cell
It basically says "don't format it if both start & end dates are before this 7 day period, or both start & end dates are after this 7 day period."
Replace "G" with the first date column. Make sure the dates are below the header row or it won't work.
For some reason I needed to use semicolons instead of commas in the formula. So if you keep getting "parse error" - you might wanna try replacing the commas with semicolons.