Topics Map > IT Help Desk Services > Applications
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.
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:
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...".