Formula Errors

How to Keep an Excel Formula Cell Blank When the Source is Empty

Rana Garcia Sep 27, 2026 869 views

Question details

The user needs an Excel formula to return a completely blank cell instead of generating a default 1900 date when the referenced source cell has no data.

How to Keep an Excel Formula Cell Blank When the Source is Empty

Product: Excel
Device & OS: not provided
Scenario: Using date calculation formulas, such as EDATE, to project future dates based on a source cell that may not have user input yet.
Observed behavior: The formula evaluates an empty source cell as zero, which Excel's date system translates into an unintended date from the year 1900.

Before you start

Verify that your source cells are completely empty. If a cell contains a hidden space character, the formula might return a #VALUE! error instead of recognizing it as blank.

Solution 1Recommended

Use an IF Statement to Check for Blank Cells

Wrap your existing calculation in an IF function to explicitly tell Excel to return a blank text string if the source cell contains no data.

This is the most reliable method to prevent zero-value date translation. By checking the cell condition first, the formula only executes the mathematical operation when valid data is present.

Select the target cell

Click on the cell where you want the formula result to appear.

Start the IF function

Type `=IF(E10="","",` into the formula bar. Replace 'E10' with your actual source cell reference. The two double quotes represent a blank text string.

Add your original formula

Right after the second comma, append your original formula, such as `EDATE(E10,6)`.

Complete the formula

Close the parenthesis so your final formula looks like `=IF(E10="","",EDATE(E10,6))`, then press Enter.

Use an IF Statement to Check for Blank Cells

Formula Applied: The cell will now remain blank and only calculate the date when you input a valid date in the source cell.

Solution 2

Use the ISBLANK Function as an Alternative

Combine the ISBLANK function with an IF statement for a strict evaluation of whether a cell is entirely empty.

Seamless Spreadsheet Management

Easily Manage Complex Formulas with WPS Office

WPS Spreadsheet provides a highly intuitive environment for building, debugging, and managing complex logical formulas like IF and ISBLANK to keep your datasets perfectly clean.

1. Open your spreadsheet in WPS Office: Launch WPS Spreadsheet and open the file containing your date calculations.
2. Select the error cell: Click on the cell displaying the unwanted 1900 date.
3. Update the formula: In the top formula bar, wrap your existing calculation with the IF statement: `=IF(E10="","",EDATE(E10,6))`.
4. Press Enter to execute: Hit Enter on your keyboard. The cell will immediately update to a clean, blank format.

100% format compatibility with Microsoft Excel formulas and functions.Built-in advanced formula editor to prevent syntax and logic errors.Free, lightweight, and fast alternative to heavy spreadsheet applications.Easily apply conditional logic to prevent unwanted 1900 date outputs.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my date formula return a date in the year 1900?

Spreadsheet software processes dates as sequential serial numbers. The system starts at January 1, 1900. When a formula references a blank cell, it evaluates that blank space as a zero. The serial number zero defaults to January 0, 1900.

Can I use the IFERROR function to hide the 1900 date?

No. The IFERROR function only triggers when a formula generates a true error code (like #VALUE!, #N/A, or #DIV/0!). Because 1900 is technically a valid calculated date output, IFERROR ignores it. You must use the IF function to evaluate the blank cell instead.

How do I fix a #VALUE! error after adding the IF statement?

A #VALUE! error usually means the referenced source cell isn't actually blank, but instead contains invisible characters, such as a space typed by accident. Select the source cell, press the Delete key to clear all contents, and the formula should work correctly.

Excel & Data Problems