Forum Discussion
Excel - conditional formatting help
Hi Please help,
I have created a spreadsheet and have formulas when a date is enter it projects 30 day and 90 days into their respective columns. I would like to conditional format these columns to reflect when the projected dates are coming up to being due e.g. 5 days, and over due. I have been using the "today" formula and have now realised this is not correct. PLEASE HELP!
Here is a snip of the sheet to provide further context;
Column C's date is plus ninety days in Column F
Column E is +30 days from column D
I need column's E & F to show green 5 days prior to the projected date in these columns and red once the projected date passes (overdue).
Thanks heaps
Rosemarie
4 Replies
salway42 Sorry, but I don't follow your dates here. You mention that column F = C plus 90 days, but I guess you mean column G = C plus 90 days? You are using a date format d/mm/yyyy aren't you?
Similarly, you say that E = D plus 30 days. I guess, you mean F = E plus 30 days. And where do I find the "projected date"? And is it E and F you want to conditionally format, or perhaps F and G or G and H?
Any how, you should create two rules. One that checks if the cell value is between the "projected date" minus 5 days and the "projected date" (green). Another rule needs to check if the cell value is equal or greater than the "projected date" (red).
- Ok thanks a bundle 🙂
salway42 Difficult to be sure without seeing your file, but a CF rule highlighting cells where the value is less than TODAY( )+5 should give you the desired outcome. It marks all passed dates and the ones for the coming 5 days.
(Picture taken on a Mac, but similar on PC)
Thank you, I have snipped a copy of my spreadsheet to provide further context.
Are you saying I can use the "=today()" formula even though the dates do not have anything to do with today, but the actual date in the column?
