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:
Best Office Productivity Tools
Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel
Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...
Supports Office/Excel 2007-2021 & newer, including 365 | Available in 44 languages | Enjoy a full-featured 30-day free trial.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!