Formula Errors

How to Display YES or NO Based on Date in Excel

Guest Writer Oct 12, 2026 869 views

Question details

The user wants to output the text YES or NO based on whether a date meets a certain condition, rather than trying to read the cell's conditional formatting color.

How to Display YES or NO Based on a Date Instead of Cell Color in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Evaluating dates in a spreadsheet to generate text labels instead of relying on conditional formatting colors.
Observed behavior: Excel formulas cannot directly read or evaluate a cell's background color reliably, so the underlying date logic must be used to generate the output instead.

Before you start

Identify the exact logical condition used for your existing conditional formatting (e.g., date is older than 45 days). Ensure your target column where the YES or NO will appear is formatted as General text.

Solution 1Recommended

Use the IF Function to Evaluate the Date Logic

Replicate the conditional formatting logic inside an IF statement to return YES or NO based on the date.

Because standard Excel functions cannot directly detect cell colors applied by conditional formatting, the most reliable method is to evaluate the data itself. By using the exact same date condition that triggers the color change, you can output specific text strings.

Select the Result Cell

Click on the cell where you want the YES or NO text to appear (e.g., cell E2 next to your date in D2).

Enter the IF Formula

Type the formula =IF(D2="","",IF(D2<=TODAY()-45,"YES","NO")). This formula first checks if the cell is blank to prevent errors, then checks if the date is at least 45 days old relative to today.

Apply the Formula to the Column

Press Enter to calculate the result, then drag the fill handle at the bottom-right corner of the cell down to copy the formula to the remaining rows in your dataset.

Use the IF Function to Evaluate the Date Logic

Matching Logic: Make sure the condition in your IF formula exactly matches the rule you set in Conditional Formatting so the text output and cell colors align perfectly.

Free Spreadsheet Software

Effortlessly Manage Date Formulas with WPS Spreadsheet

WPS Spreadsheet offers robust formula support, allowing you to easily evaluate date conditions and apply both logical text outputs and conditional formatting seamlessly.

1. Open your document: Launch WPS Spreadsheet and open the file containing your date values.
2. Enter the IF formula: Select the target cell and input =IF(D2="","",IF(D2<=TODAY()-45,"YES","NO")) into the formula bar.
3. Copy the formula: Drag the fill handle down to apply the logic across your entire dataset instantly.

100% compatible with Microsoft Excel formulas and functionsIntuitive formula builder with built-in syntax highlightingLightweight application that runs smoothly on any deviceFree built-in conditional formatting and data visualization tools

microsoft office alternative - wps office

Frequently Asked Questions

Can Excel formulas return a value based on a cell's conditional formatting color?

Standard Excel functions cannot directly read or evaluate a cell's background color or font color applied by conditional formatting. You must use the underlying data logic (like a specific date range) to return values.

How do I change the condition from 45 days to another number?

Simply replace the number 45 in the formula =IF(D2<=TODAY()-45,"YES","NO") with your desired number of days, such as 30 or 60, depending on your project requirements.

Why does my date formula return an error or incorrect value for blank cells?

If you only use =IF(D2<=TODAY()-45,"YES","NO"), blank cells are treated as zero (January 0, 1900), which is always less than today's date. Adding IF(D2="","",...) at the beginning prevents this by leaving the cell blank if there is no date entry.

Excel & Data Problems