Formatting Issues

How to Highlight Excel Dates Red, Yellow, and Green with Conditional Formatting

Algirdas Jasaitis Sep 30, 2026 869 views

Question details

The user wants to automatically highlight expiration dates in a spreadsheet with red, yellow, or green colors based on how many days are remaining.

How to Highlight Excel Dates Red, Yellow, and Green Using Conditional Formatting

Product: Excel
Device & OS: not provided
Scenario: Tracking approaching deadlines, document expirations, or project milestones using a visual color-coded traffic light system.
Observed behavior: Setting up custom formula rules using the TODAY() function to calculate remaining days, and ensuring the spreadsheet is saved in a format that supports styling.

Before you start

Ensure your file is saved as an Excel Workbook (.xlsx) rather than a CSV (.csv). CSV files are plain-text and will permanently lose all conditional formatting colors and rules upon saving.

Solution 1Recommended

Apply Custom Formula Rules for Date Highlighting

Use the 'New Rule' feature in conditional formatting to set up specific date calculations for approaching deadlines.

By utilizing the TODAY() function within conditional formatting rules, Excel can dynamically calculate the number of days remaining between the current date and your specified deadline. This ensures your color-coding updates automatically each time you open the document.

Select the target date range

Click and drag to select the cells containing your dates. For example, select column A starting from cell A1.

Open Conditional Formatting

Navigate to the Home tab on the ribbon ribbon, click on Conditional Formatting, and select New Rule from the dropdown menu.

Choose the formula option

In the New Formatting Rule dialog box, select the option 'Use a formula to determine which cells to format'.

Set the Red rule (0-7 days remaining)

Enter the formula =AND(A1-TODAY()<=7, A1-TODAY()>=0) into the input box. Click the Format button, choose a Red fill color under the Fill tab, and click OK.

Set the Yellow rule (8-14 days remaining)

Repeat the process to create another New Rule. Enter the formula =AND(A1-TODAY()<=14, A1-TODAY()>=8), click Format, and apply a Yellow fill color.

Set the Green rule (Over 14 days remaining)

Create a final New Rule with the formula =A1-TODAY()>14, click Format, and select a Green fill color to indicate safe dates.

Apply Custom Formula Rules for Date Highlighting

Cell References: Make sure to replace 'A1' in the formulas with the actual first cell of your selected date range. Ensure the cell reference is relative (e.g., A1, not $A$1) so the rule applies correctly to the subsequent rows.

Manage Spreadsheets Easily

Use WPS Spreadsheet to Highlight Dates Easily

WPS Office Spreadsheet provides intuitive conditional formatting tools that work identically to Excel, allowing you to highlight deadlines and expiration dates seamlessly using custom formulas.

1. Open your dataset: Launch WPS Spreadsheet and open your .xlsx workbook containing the dates.
2. Access conditional formatting: Highlight your date cells and navigate to Home > Conditional Formatting > New Rule.
3. Enter your date formula: Select 'Use a formula', input your target date calculation such as =AND(A1-TODAY()<=7, A1-TODAY()>=0).
4. Format and apply: Click the Format button to choose your desired highlight color, then click OK to activate the traffic light system.

Fully compatible with Microsoft Excel (.xlsx) file formats and complex formulas.Built-in conditional formatting tools to easily set up custom date expiration rules.Lightweight, fast, and completely free to download.

microsoft office alternative - wps office

Frequently Asked Questions

Why did my conditional formatting disappear after saving my file?

If you originally saved your document as a CSV file, it will not retain formatting. CSV is a plain-text format designed only for storing raw data. You must use 'Save As' and choose Excel Workbook (.xlsx) to preserve your conditional formatting rules and colors.

Why are all my dates highlighted the same color despite using the formula?

This usually happens if you used absolute references (like $A$1) instead of relative references (like A1) in your formatting formula. When absolute references are used, Excel evaluates the same single cell for every row in your selection. Edit your rule and remove the dollar signs so the formula adapts to each row.

Can I use preset date formatting instead of typing formulas?

Yes, standard spreadsheet software offers preset options under Conditional Formatting > Highlight Cells Rules > A Date Occurring (e.g., 'Next month', 'Next week'). However, writing custom formulas gives you much more precise control over specific timeframes, such as exactly 14 days remaining.

Document & File Issues