Topics Map > GBIT Service Desk Services > Applications
Excel Conditional Formatting - Dynamically Color-Coding 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.
Scenario: As the current date changes, TODAY(), the conditional format (formula) will calculate the number of days between the current date and the last contact date. When a condition is TRUE the cell will dynamically color-code.
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:
1. Select the range of cells to apply the conditional format.
***NOTE: in this example, select the dates in Column J)***
2. From the Excel menu, Select "Conditional Formatting" and then "New Rule..."
3. Select "Use a formula to determine which cells to format". Enter 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 Select "OK."
***NOTE: 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...".***