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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
=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","")))
 
Upvote 0
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:
Upvote 0
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."
 
Upvote 0
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",""))
 
Upvote 0
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! :):):):)
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

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