Topics Map > GBIT Service Desk Services > Applications
Excel Conditional Formatting - Dynamically Color-Coding a Range of Dates
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 example.png](/images/group91/71834/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 example-formulas.png](/images/group91/71834/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)***
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."
![green-lessThan90.png green-lessThan90.png](/images/group91/71834/green-lessThan90.png)
![additionalRules.png additionalRules.png](/images/group91/71834/additionalRules.png)
![newRule.png newRule.png](/images/group91/71834/newRule.png)
![over90days.png over90days.png](/images/group91/71834/over90days.png)
![orderOfConditions.png orderOfConditions.png](/images/group91/71834/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...".***