Formula Errors

Excel Formula to Confirm Three Date Cells Are Filled

Chanuka Geekiyanage Sep 28, 2026 869 views

Question details

The user needs an Excel formula that returns 'Yes' (and displays in green) only when three specific, nonconsecutive cells are filled with dates, and returns 'No' if any of the cells remain blank.

How to Create an Excel Formula to Confirm Three Date Cells Are Filled

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Validating data entry to ensure all required nonconsecutive date fields are fully populated before proceeding.
Observed behavior: The user's previous formula incorrectly returned 'Yes' when only two out of the three cells were filled, instead of strictly requiring all three.

Before you start

Identify the exact cell references for your three date fields (for example, B2, D2, and F2) before applying the formula to ensure the validation logic targets the correct cells.

Solution 1Recommended

Use the IF and OR Functions with Conditional Formatting

Combine the IF and OR functions to check for blank cells, then apply conditional formatting to highlight the valid 'Yes' result in green.

By using the OR function, you can check multiple nonconsecutive cells simultaneously. If any of the specified cells are completely blank, the OR function triggers the IF statement to return 'No', solving the issue where partial completions returned a false positive.

Enter the IF and OR formula

Select the cell where you want the validation result to appear. Type =IF(OR(B2="",D2="",F2=""),"No","Yes") (replace B2, D2, and F2 with your actual date cells) and press Enter.

Open Conditional Formatting

Select the result cell, navigate to the 'Home' tab on the ribbon, and click 'Conditional Formatting'. Choose 'Highlight Cells Rules' and then click 'Equal To'.

Set the green fill rule

In the dialog box, type Yes in the first field. In the formatting dropdown, select 'Custom Format'. Go to the 'Fill' tab, choose a green color, and click 'OK' to apply the formatting.

Use the IF and OR Functions with Conditional Formatting

Formula Logic Explained: This logic guarantees that even if 2 out of 3 cells are filled, the formula will still correctly evaluate the remaining blank cell and return 'No'.

WPS Spreadsheet Solution

Easily Manage Formulas and Formatting with WPS Office

You can effortlessly apply advanced logical formulas and conditional formatting to validate date entries using WPS Spreadsheet, a highly compatible and lightweight alternative.

1. Open your spreadsheet in WPS Office: Launch WPS Spreadsheet and open the workbook containing your nonconsecutive date cells.
2. Input the logical formula: Select the target cell and input =IF(OR(B2="",D2="",F2=""),"No","Yes") to evaluate the blank conditions.
3. Apply Conditional Formatting: Navigate to Home > Conditional Formatting > Highlight Cells Rules > Equal To, enter 'Yes', and format it with a green fill.

100% compatible with Microsoft Excel formulas like IF, OR, and AND.Intuitive Conditional Formatting rules for visual data analysis.Free and lightweight software ideal for daily spreadsheet tasks and data validation.

microsoft office alternative - wps office

Frequently Asked Questions

How do I check if consecutive cells contain dates?

For a continuous range, you can use the COUNT function combined with IF. For example, =IF(COUNT(B2:D2)=3,"Yes","No") will check if exactly three cells in the range B2:D2 contain numbers (which includes dates).

Why does my formula return 'Yes' when cells have spaces instead of dates?

The formula checks for strictly empty cells (""). If a cell contains a space character, the spreadsheet reads it as text, not blank. You can use the TRIM function or combine it with ISBLANK to prevent spaces from causing a false 'Yes'.

Can I format the 'No' result in red automatically?

Yes. While the result cell is selected, add a second Conditional Formatting rule. Choose 'Equal To', type 'No', and select a red fill format from the dialog box.

Excel & Data Problems