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

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. (in this example, select the dates in Column J)

dates2.png

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

cf-menu.png

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.  

*Current date, TODAY(), is updating as your computer updates time and date.

green-lessThan90.png

3. To add more conditions to each cell, again select Conditional Formatting, but this time, select "Manage Rules...

additionalRules.png

4. Select "New Rule..."

newRule.png

5. Go through it again (steps 2,3 and 4) 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 

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

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
Owner:Patricia T.Group:UW Green Bay
Created:2017-03-17 14:30 CDTUpdated:2017-03-17 16:21 CDT
Sites:UW Green Bay
Feedback:  0   0