Multi IF Formula within Single Cell

Matalyn

New Member
Joined
Oct 17, 2014
Messages
24
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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?
 
Upvote 0
Ok, I think (hope) I got it:

Code:
[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]
 
Upvote 0
Really hope this works!

Code:
[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]
 
Upvote 0
Really hope this works!

Code:
[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.
 
Upvote 0

Forum statistics

Threads
1,215,699
Messages
6,126,273
Members
449,308
Latest member
VerifiedBleachersAttendee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top