Forum Discussion
Help with Conditional formatting
I have a spreadsheet with various info and one column has dates. I would like to set up conditional formatting for the following
date in cell is 20 days before today turn yellow
date in cell is 25 days before today turn orange
date in cell is 30+ days before today turn red & bold
Basically I have due dates of 30 days and I would like to know which dates are coming up to being due and which dates are past due based off of the date I go into the spreadsheet...
Could someone please help me with this?
Much appreciated!!
Lindsay,
It looks like the format painter was still referencing column A, and the last two were missing the <. I've corrected the formulas, and also added a rule to handle blank cells. I've taken the liberty of adding a sheet to your workbook, and copying your info to a table. This is an instance that I would recommend using a table for your data, because the conditional formatting 'Applies to' section will grow and shrink as rows are added and removed. If you press tab in the bottom right cell of the table, it will automatically add a new row, and extend the formatting, and any column formulas you may use to the next row. Just something to consider, and if you decide not to use it, you can just delete the tab that says Table Version
pls see attached sample.
hope it will work for you.
thanks..
- the dates in column A is referenced to 9-26-2018 (today) - the resulting difference of day may change per actual data entered..
Hi,
Thank you for your response, but it does not seem to work for me. I have attached the spreadsheet minus all the other information in it other than the dates. This is after I copied and pasted the format.
Lindsay,
It looks like the format painter was still referencing column A, and the last two were missing the <. I've corrected the formulas, and also added a rule to handle blank cells. I've taken the liberty of adding a sheet to your workbook, and copying your info to a table. This is an instance that I would recommend using a table for your data, because the conditional formatting 'Applies to' section will grow and shrink as rows are added and removed. If you press tab in the bottom right cell of the table, it will automatically add a new row, and extend the formatting, and any column formulas you may use to the next row. Just something to consider, and if you decide not to use it, you can just delete the tab that says Table Version
