How to conditional formatting red amber green based on date in Excel?
If you have a list of dates in a worksheet, you would like to highlight the date with red, amber and green colors with conditional formatting. For example, if the date is past, then show red; if the future dates within 3 months from now show amber color; and if the future date is more than 3 months from now, the green color is displayed as following screenshot shown. How could you solve this job in Excel as quickly as you can?
To conditional formatting a list of date with red, amber and green colors like the traffic light system, please do with the below steps:
1. Select the date column, and click Home > Conditional Formatting > New Rule, see screenshot:
2. In the New Formatting Rule dialog box, click Use a formula to determine which cells to format option in the Select a Rule Type list box, and then enter this formula: =A2<TODAY() into the Format values where this formula is true text box, see screenshot:
3. Then click Format button to go to the Format Cells dialog, under the Fill tab, select the red color, see screenshot:
4. And then click OK > OK buttons to close the dialogs, and now, the past dates in the column have been highlighted with red color, see screenshot:
5. To highlight the future dates within 3 months from now, go to the New Formatting Rule dialog box, and enter this formula: =AND(A2>=today(),A2<edate(today(),3)) into the Format values where this formula is true text box, see screenshot:
6. And then click Format and choose an amber color in the Format Cells dialog, click OK > OK, and the dates within 3 months from now have been highlighted as below screenshot shown:
7. To highlight the date more than 3 months from now, you just need to apply this formula: =A2>EDATE(TODAY(),3) into the New Formatting Rule dialog box, see screenshot:
8. And choose the green color to conditional formatting the date more than 3 months in the Format Cells dialog, and you will get the result as following screenshot shown: