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


example.png

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

example-formulas.png

FOLLOW THESE STEPS:




1. Select the range of cells to apply the conditional format.

***NOTE: in this example, select the dates in Column J)***
dates2.png

2. From the Excel menu, Select "Conditional Formatting" and then "New Rule..."


cf-menu.png

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: Current date, TODAY(), is updating as your computer updates time and date.***

green-lessThan90.png
4. To add more conditions to each cell, again Select "Conditional Formatting," but this time, Select "Manage Rules..." 
additionalRules.png
5. Select "New Rule..."

newRule.png
6. Repeat these steps again, this time the formula is checking for dates that are now over 180 days; if the formula evaluates TRUE, the color-code will change\update to RED.

over90days.png 
7. In this last example, a condition is added to test for dates that are between 90 and 180. Those will appear yellow. 
 
NOTE: The rules are applied in the order shown.

orderOfConditions.png

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

editRule.png


Keywords:
conditional conditional formatting Excel date ranges color-code date ranges
Doc ID:
71834
Owned by:
Steve K. in UW Green Bay
Created:
2017-03-17
Updated:
2022-09-19
Sites:
UW Green Bay