Excel - conditional formatting to dynamically color-code a range of dates.
You need to have a daily color-coded look at what's 'past due'! Do you need to color code for a range of dates? (for example, 'Green' for less than 30 days; 'Yellow' for between 90-180 days; and Red for over 180 days. Apply these conditional formats.
Column J is the ONLY necessary column --- columns K, L, M, & N are only to help you understand what the conditional format is testing. All 3 conditional format are contained in each cell. (cell J2, J3, etc...)
Here you see the formulas that will actually be evaluated together, in each cell; the color-code will be determined by the evaluation of TRUE or FALSE. *Reminder - Columns K, L, M, N are only here for explanation purposes...
FOLLOW THESE STEPS:
2. Select "Use a formula to determine which cells to format". Type the appropriate formula depending on your needs. The example below is determining if the cell contains a date that is less than 3 months past the current date. (90 days). Select the Format tab and select whatever color code you want. Then click, OK.
To edit any of the conditions, open "Conditional Formatting..., then "Manage Conditions..." and click on the particular Rule you need to alter and select "Edit Rule...".