Multi IF Formula within Single Cell

[Matalyn]

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

 

[t0azt]

Can you post some sample data please?

 

[Matalyn]

How do I attach a file on here?

Can you post some sample data please?

 

[Matalyn]

If this helps here:

Formula works for this data...
Todays date = 10/17
Start Date = 10/13
Estimated Completion Date (ECD)= 10/20
Status= In progress

Formula doesn't work for this data...
Todays date = 10/17
Start date = 10/13
Estimated Completion Date (ECD)= 10/15
Status = In Progress

Side note: Say the data was as follows...j
Todays date= 10/17
Start date = 10/13
Estimated completion date= 10/16
Actual Completion date = 10/14
Status= Completed
Is there a way to have the green color stop overriding the gray once the status is changed to completed? This way I can see the amount of time we overestimated?

 

[t0azt]

Ok, I think (hope) I got it:

[COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]if[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]AND[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]J$2[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]>=[/FONT][/COLOR][COLOR=#7E3794][FONT=Inconsolata]$D3[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]J$2[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]<=[/FONT][/COLOR][COLOR=#11A9CC][FONT=Inconsolata]$E3[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]if[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]J$2[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]<=[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]today[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"green"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"gray"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]if[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]And[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]OR[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#A61D4C][FONT=Inconsolata]$B3[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"In Progress"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#A61D4C][FONT=Inconsolata]$B3[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"On Hold"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]today[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]>=[/FONT][/COLOR][COLOR=#11A9CC][FONT=Inconsolata]$E3[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]J$2[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]>[/FONT][/COLOR][COLOR=#11A9CC][FONT=Inconsolata]$E3[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]today[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]>=[/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]J$2[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"Red"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]""[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])

Now "red" will no show up before the start date or after today...Thats part 1 I´ll see what I can manage for the completed status, just let me know if this worked first.[/FONT][/COLOR]

 

[t0azt]

Really hope this works!

[COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]if[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]AND[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]J$2[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]>=[/FONT][/COLOR][COLOR=#7E3794][FONT=Inconsolata]$D3[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]J$2[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]<=[/FONT][/COLOR][COLOR=#11A9CC][FONT=Inconsolata]$E3[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]if[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]and[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#A61D4C][FONT=Inconsolata]$B3[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"completed"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]J$2[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]>=[/FONT][/COLOR][COLOR=#4285F4][FONT=Inconsolata]$H3[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"gray"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]if[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]J$2[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]<=[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]today[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"green"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"gray"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]if[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]And[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]OR[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#A61D4C][FONT=Inconsolata]$B3[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"In Progress"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#A61D4C][FONT=Inconsolata]$B3[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"On Hold"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]today[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]>=[/FONT][/COLOR][COLOR=#11A9CC][FONT=Inconsolata]$E3[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]J$2[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]>[/FONT][/COLOR][COLOR=#11A9CC][FONT=Inconsolata]$E3[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]today[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]>=[/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]J$2[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"Red"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]""[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR]

 

[Matalyn]

Really hope this works!

[COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]if[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]AND[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]J$2[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]>=[/FONT][/COLOR][COLOR=#7E3794][FONT=Inconsolata]$D3[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]J$2[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]<=[/FONT][/COLOR][COLOR=#11A9CC][FONT=Inconsolata]$E3[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]if[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]and[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#A61D4C][FONT=Inconsolata]$B3[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"completed"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]J$2[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]>=[/FONT][/COLOR][COLOR=#4285F4][FONT=Inconsolata]$H3[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"gray"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]if[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]J$2[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]<=[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]today[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"green"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"gray"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]if[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]And[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]OR[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#A61D4C][FONT=Inconsolata]$B3[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"In Progress"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#A61D4C][FONT=Inconsolata]$B3[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"On Hold"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]today[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]>=[/FONT][/COLOR][COLOR=#11A9CC][FONT=Inconsolata]$E3[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]J$2[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]>[/FONT][/COLOR][COLOR=#11A9CC][FONT=Inconsolata]$E3[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]today[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]>=[/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]J$2[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"Red"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]""[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR]

The formula you have in there now, which is...
=if(AND(J$2>=$D3,J$2<=$E3),if(and($B3="completed",J$2>=$H3),"gray",if(J$2<=today(),"green","gray")),if(And(OR($B3="In Progress",$B3="On Hold"),today()>=$E3,J$2>$E3,today()>=J$2),"Red",""))

works great! I tested it out with the completed. and It seems to be fine. If I find anything else, I'll let you know. Seriously, you've been sooo helpful.