Forum Discussion
Conditional formatting using date passed and value in another column
Hi please could anyone help with this - I'm trying to use an 'AND' formula but seem to be messing it up
I have a sheet tracking projects, showing the date open - the date is in column B, and column C has the formula =TODAY()-B2
I use column i to track the level of project, - anything that's level 1-6 should complete within 60 days. And anything level 7-9 should complete within 90 days
How can i set a formula to highlight (in conditional formatting) the values in column C based on the above - basically any project up to level 6 that's over 60 days is highlighted in red, and any level 7-9 projects over 90 days the same?
Any help REALLY appreciated - thanks!
8 Replies
Select the cells in column C, starting in C2. C2 should be the active cell in the selection.
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Leave the first drop down set to 'Cell Value'.
Select 'greater than' from the second drop down.
In the box next to it, enter the formula
=IF(I2<7, 60, 90)
Click Format...
Activate the Fill tab.
Select red.
Click OK, then click OK again.
- Thanks Hans, appreciated. Think this would highlight anything with specific days rather than looking at eg level 6 projects that are over 60 days or level 7 projects over 90 days. Since solved. Thanks again, it's really useful learning as can imagine the formula you've shown will also come in handy in future 🙂
Have you actually tried my suggestion? It's simpler than Riny_van_Eekelen's (excellent!) solution.
jimmybii I believe these rules will achieve what you describe.
Assuming your date starts on row 3. Example file attached.
- Absolutely brilliant - thanks so much, have been trying to crack this for weeks until discovering this forum!
