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: