Mar 15 2021 10:39 AM
@members
recommendations will be great
I have timelines in excel with 3 different dates.
Action: I want to be able to grade the timelines to reflect the 2 differences as in line 9
How do I combine following outcomes needed below so I can conditional format by Y and X
Mar 15 2021 10:55 AM
Mar 15 2021 11:00 AM
Mar 15 2021 11:45 AM
Hi Mtarler
so this formula gives me the separations =IF(AND(AE$5>=$G9,AE$5<=$H9),"X",IF(AND(AE$5>=$H9,AE$5<=$I9),"Y",""))
I am now struggling to show the cut off to apply to the rows below in the conditional formating. Not sure where I am getting it wrong
I want to format the cells with X different colour and the cells with Y different colour
Mar 15 2021 12:05 PM
Mar 15 2021 12:15 PM
Mar 15 2021 12:48 PM
Mar 15 2021 01:14 PM
Mar 15 2021 01:33 PM
@Khadd3243 no problem, that is why we volunteer. so I didn't see any conditional formatting on the sheet so i added 2 rules. Each was Applied to columns H:CK as shown in the conditional rules manager box below:
You can also see the formula that it is using on the left.
BTW, I formatted both the text color and background color the same so all those "X" and "Y" values 'disappear'
To modify, just click the 'Edit rule...' button and you will get a box like this:
you can see on the upper half that the 'Format only cells that contain' option was selected and on the lower half I specified Cell Value = "Y" as the condition. To modify the formatting options like the text color and background color just click the Format button.
You can also see the last option on the upper half says "Use a formula to determine which cells to format" and that is the custom formula option I mentioned earlier whereby you could use the conditionals that determine if the cell should be an "X" or "Y" to DIRECTLY determine if that cell should be Dark Green or Light Green. If you ever decide to use that, just remember to make the formula based on the upper left corner cell from the Applies To section just like the formula you made to be inside the cell before you fill down and fill right.
Hope that all helps.
Mar 21 2021 06:52 AM