Excel - conditional formatting help

Copper Contributor

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;

salway42_0-1601015472207.png

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 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.

Screenshot 2020-09-25 at 08.14.37.png

(Picture taken on a Mac, but similar on PC)

@Riny_van_Eekelen 

 

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?

@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 :)