Hi,
So, I'm creating a gantt chart in google spreadsheets and need to have bars fill in specific colors based on the start date, estimated completion date, todays date, and the tasks progress. In other words, I need the time between the start date and estimated completion date to be gray. As todays date passes over this timeframe, all days between today and start date automatically turn green while all days between today and estimated completion date stay gray, up until those days pass. Also, if the task takes longer than expected, (the task will be either marked in progress or on hold), then consecutive days(cells) after the estimated completion date will be red, up until marked "completed", in which the cells will no longer be filled with any color.
Here is the If statement layout in words that I currently have...
(#1)If the cell >= start date AND cell <=Estimated Completion date, then go to IF statement #2, If not, go to IF statement #3
(#2) If cell <=today(), then "green", if cell>=today(), "gray"
(#3) If other cell is marked "in progress" or "on hold" AND today>Estimated completion date, then turn "red"
If not, then "blank".
Here is the formula that I currently have (in excel it currently says ERROR (forumula parse error))...
=if(AND(J$2>=$D3,J$2<=$E3),if(J$2<=today(),"green","gray"), if(And($B3="In Progress" or "On Hold",today()>=$E3,"Red","")))
I know how to do the conditional formatting for the colors, so i just need the text "green", "gray", and "red" to show up in the cells.
Please Help!! URGENT.
I've also attached an example of my layout so you know the correct cells.
https://docs.google.com/a/sgcmail.c...dT1qIMQDwdjUTIT-PKZjp7DhU/edit#gid=1103964213
So, I'm creating a gantt chart in google spreadsheets and need to have bars fill in specific colors based on the start date, estimated completion date, todays date, and the tasks progress. In other words, I need the time between the start date and estimated completion date to be gray. As todays date passes over this timeframe, all days between today and start date automatically turn green while all days between today and estimated completion date stay gray, up until those days pass. Also, if the task takes longer than expected, (the task will be either marked in progress or on hold), then consecutive days(cells) after the estimated completion date will be red, up until marked "completed", in which the cells will no longer be filled with any color.
Here is the If statement layout in words that I currently have...
(#1)If the cell >= start date AND cell <=Estimated Completion date, then go to IF statement #2, If not, go to IF statement #3
(#2) If cell <=today(), then "green", if cell>=today(), "gray"
(#3) If other cell is marked "in progress" or "on hold" AND today>Estimated completion date, then turn "red"
If not, then "blank".
Here is the formula that I currently have (in excel it currently says ERROR (forumula parse error))...
=if(AND(J$2>=$D3,J$2<=$E3),if(J$2<=today(),"green","gray"), if(And($B3="In Progress" or "On Hold",today()>=$E3,"Red","")))
I know how to do the conditional formatting for the colors, so i just need the text "green", "gray", and "red" to show up in the cells.
Please Help!! URGENT.
I've also attached an example of my layout so you know the correct cells.
https://docs.google.com/a/sgcmail.c...dT1qIMQDwdjUTIT-PKZjp7DhU/edit#gid=1103964213