Function Problems

How to Create an Excel Formula That Displays a Value Only When Another Cell Is Blank

Jason Miller Oct 16, 2026 870 views

Question details

The user needs a formula to calculate the days remaining or overdue based on a due date, but the formula should display nothing (be blank) if a task completion date has been entered in another cell.

How to Display a Value Only When Another Cell Is Blank in Excel

Product: Excel
Device & OS: not provided
Scenario: Tracking task due dates and hiding the overdue or days remaining calculation once a completion date is entered to keep the spreadsheet clean.
Observed behavior: The user wants a target cell to dynamically output a calculation result or remain completely blank depending on the empty status of a secondary input cell.

Before you start

Ensure that your due date and completion date cells are properly formatted as 'Date', and that your formula cell is formatted as 'General' or 'Number' to prevent date format errors in your results.

Solution 1Recommended

Use the IF Function with an Empty String

The most straightforward way to conditionally display a value based on another cell's blank status is using the IF function combined with an empty string.

The IF function allows you to test a condition and return one value if true, and another if false. By testing if a cell equals "" (an empty string), you can command Excel to execute your date calculation. If the cell contains data, you can output "" to make the cell appear empty.

Select the target cell

Click on the cell where you want the calculation result to appear (for example, cell A3).

Enter the IF formula

Type the formula: =IF(A2="", TODAY()-A1, ""). This tells the spreadsheet to check if the completion date cell (A2) is empty.

Apply the calculation

Press Enter. If A2 is blank, the cell will display the days calculated from TODAY()-A1. Once you type a date into A2, the formula cell will immediately become blank.

Use the IF Function with an Empty String

Understanding the empty string: The double quotes ("") at the end of the formula instruct the spreadsheet to output a completely blank text string when the condition is false, leaving your cell visually empty.

Solution 2

Use the ISBLANK Function for Stricter Blank Checks

The ISBLANK function specifically checks if a cell is truly empty, which is useful if your cells might contain hidden formatting or formulas.

WPS Spreadsheet Solution

Easily Manage Conditional Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports advanced logical functions like IF and ISBLANK, allowing you to seamlessly calculate dynamic task trackers. It is highly compatible with Microsoft Excel formulas, making it easy to build intelligent spreadsheets.

1. Open your tracker in WPS: Launch WPS Spreadsheet and open your task tracking document.
2. Select your formula cell: Click on the cell where you want the days overdue or days remaining to appear.
3. Apply the conditional formula: Type =IF(A2="", TODAY()-A1, "") and hit Enter to instantly apply the logic to your worksheet.

Fully compatible with Microsoft Excel formulas and .xlsx formats.Built-in function helper to easily construct complex IF statements without syntax errors.Lightweight, fast, and completely free to use for daily spreadsheet tasks.

Frequently Asked Questions

Why does my formula return an error like #VALUE! instead of being blank?

This usually happens if the due date cell (A1) contains standard text instead of a valid date, or if it has hidden spaces. Ensure A1 is formatted as a Date and contains valid date values.

Can I display specific text like 'Completed' instead of a blank cell?

Yes. You can easily modify the formula to include your preferred text in the false condition. Instead of double quotes at the end, use text wrapped in quotes: =IF(A2="", TODAY()-A1, "Completed").

Does this formula work if my 'blank' cell accidentally contains a hidden space?

No. If a cell contains a spacebar character, Excel does not consider it blank. If this happens often, you can use the TRIM function to ignore spaces by writing: =IF(TRIM(A2)="", TODAY()-A1, "").

Excel & Data Problems