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
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

t0azt

Board Regular
Joined
Aug 1, 2012
Messages
134
=if(AND(J$2>=$D3,J$2<=$E3),if(J$2<=today(),"green","gray"),if(And(OR$B3="In Progress",$B3="On Hold"),today()>=$E3,"Red","")))
 

t0azt

Board Regular
Joined
Aug 1, 2012
Messages
134
Missed a (
=if(AND(J$2>=$D3,J$2<=$E3),if(J$2<=today(),"green","gray"),if(And(OR($B3="In Progress",$B3="On Hold"),today()>=$E3,"Red","")))
 
Last edited:

Matalyn

New Member
Joined
Oct 17, 2014
Messages
24

ADVERTISEMENT

Missed a (
=if(AND(J$2>=$D3,J$2<=$E3),if(J$2<=today(),"green","gray"),if(And(OR($B3="In Progress",$B3="On Hold"),today()>=$E3,"Red","")))


hahahaha Now, it gives me #N/A...and says " Error: Wrong number of arguments to IF. Expected between 2 and 3 arguments, but got 1 arguments."
 

t0azt

Board Regular
Joined
Aug 1, 2012
Messages
134
Didnt close the second AND function. Third time´s the charm!

=
if(AND(J$2>=$D3,J$2<=$E3),if(J$2<=today(),"green","gray"),if(And(OR($B3="In Progress",$B3="On Hold"),today()>=$E3),"Red",""))
 

Matalyn

New Member
Joined
Oct 17, 2014
Messages
24

ADVERTISEMENT

Didnt close the second AND function. Third time´s the charm!

=
if(AND(J$2>=$D3,J$2<=$E3),if(J$2<=today(),"green","gray"),if(And(OR($B3="In Progress",$B3="On Hold"),today()>=$E3),"Red",""))

Yayyyyyy!!!! I'm literally jumping up and down in my office chair. Thank you a million times. I know this probably was super easy for you but it was a major feat for me to finally get it to work. I was sooo close.


Thanks again! :):):):)
 

Matalyn

New Member
Joined
Oct 17, 2014
Messages
24
ooops. Found a bug. when it turns red. When the todays date is after the estimated completion date and the project is still in progress or on hold, all the cells before the start date and all the ones after the estimated completion date turn red, instead of just the ones between the Estimated completed date and todays date.
 
Last edited:

t0azt

Board Regular
Joined
Aug 1, 2012
Messages
134
What are you inputting for the conditional formatting rules?
 

Matalyn

New Member
Joined
Oct 17, 2014
Messages
24
What are you inputting for the conditional formatting rules?


I'm not in putting any conditional rules yet. Just the text "red" shows up for all dates before the start date and all the dates after the estimated completion date, if today's date is after the estimated completion date and the task is still in progress or on hold.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,780
Messages
5,598,038
Members
414,205
Latest member
Tushark

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
Top