EXCEL - Conditional Formatting Alert Due Date?

EXCEL - Conditional Formatting Alert Due Date?


Heading in cell C1 POS Notification Date -- Cell C2 4/9/2007 (I need for this dated to go to red 60 days prior to the date shown.)





Heading in cell D1- POS Interview Date -- Cell D2 4/23/07 (I need this cell to turn green 7 days prior to date.)





Heading in cell E1 - POS Report Date -- Cell E2 5/1/2007 (I need the cell to turn blue 7 days prior to date)





Thank You

EXCEL - Conditional Formatting Alert Due Date?
This is tricky but can be accomplished even within the limitations of conditional formatting. The trick is to create another cell (usually in a hidden column) that contains the necessary if() function based upon current date comparison to cell C2 value. The result of this if() test in, say, cell Z2, should be equal to cell C2 if true and unequal if untrue. This Z2 formula looks like:





=IF($C2-60%26gt;TODAY(),$C2,$C2-1)





Now you have something in Z2 against which you can compare your value from C2, which is the basic limitation of the conditional formatting feature. Your conditional formatting criteria for cell C2 is thus:





"cell value; is greater than; =$Z2"





Your conditional formatting for this criteria being met would be set to a background pattern of red, or red font depending upon your preference.





I just tried this formula in a sample spreadsheet, and it worked perfectly even when copied to subsequent rows. As of today, all date values on or before 8/8/2007 show red becuse we are within 60 days. All dates from 8/9/2007 forward are normal font because we are not yet within 60 days.





If your question was about being within 60 days of a hard date entered in cell C1 rather than the current date, just replace "TODAY()" in the formula with "$C$1". I leave it to you to apply this same approach to your column D and E formatting.





Good luck!





I'll click your profile in a moment to see if I can E-mail you my example spreadsheet where I tested this solution. [Couldn't send the E-mail per your profile preferences.]