Help with Nested If-And formula for Gantt style project

Copper Contributor

I am trying to create a formula that identifies a color (conditionally formatted) based off of a number assigned to a cell based on a date.    

 

Set Up:  The date rolls from today forward on the top row e.g. Sep, Oct, Nov, Dec etc.  These months are in their own columns.  The Start Date, End Date, and Percentages are typed in by the user.

 

I assigned a 2,3,4,5 and 6 to the colors: Amber(Orange), Yellow, Light Blue, Light Green and Dark Green respectively.  This will pull the number to the cell so I can use it for conditional formatting.  This is not required I just did this for conditional formatting purposes.  If there is another easier way to do all of this I am game to try it.

 

Result Expected (See attached file for reference):  When a person enters the start date it places a color into the associated month (today) under the column assigned to that month.  Think Gantt chart.  The color is a percentage range so for example if a person inputs 100% in the cell, the cell under the same row but under the correct date column turns Dark Green.  If they put any percentage between 0 and 26% in the percentage column, it turns Amber Between the current date and the start date.  Also, the top date row with the rolling dates changes to the current month automatically based off of the Today() formula and all the other dates to the right of the first date automatically update.  I need the colored cell to follow those dates.  For example, when the date goes from Sep to Oct, the colored cell continues to follow Oct with the same color until the percentage is changed to indicate another color.  

 

Here is the formula I have created so far that does not work:

 

=IF(AND(E3>=0.01,E3<=0.25,Month(C3)=Month(G2),”2”,IF(AND(E3>0.26,E3<=0.5,Month(C3)=Month(G2),”3”,IF(AND(E3>=0.51,E3<=0.75,Month(C3)=Month(G2),”4”,IF(AND(E3>=.76,E3<=0.99,Month(C3)=Month(G2),”5”,IF(AND(E3=1,Month(C3)=Month(G2),”6”,””))))))))))

 

E3 is the first entry for percentages

C3 is the start date

G2 is the first cell under the rolling month columns (29SEP).

 

As for the end date, I would like for the cell to show maybe another color on that day, say black, then the following day show no color.

 

Please let me know what you think!

 

Thanks in advance for any assistance with this.

Mike

1 Reply
One more bit of info, I am using Excel 2013 (Not on 365).