Formula Errors

Keep Excel Date Cells Blank When the Source Is Zero

Chanuka GeekiyanageChanuka Geekiyanage Nov 22, 2026 868 views

Question details

Keep linked date cells visually blank when their source cells are empty or contain zero.

Keep Excel Date Cells Blank When the Source Is Zero
Product
Microsoft Excel
Device & OS
not provided
Scenario
The COST sheet references manually entered values on the Master sheet and should display dates only when the source contains a valid date.
Observed behavior
A direct reference to an empty or zero source cell can display a zero date, such as January 0, 1900, in a date-formatted destination cell.
Before you start

Identify the source sheet and cell used by the existing reference, such as Master!B4. Check that the destination cells are formatted as dates and that valid source dates are stored as Excel date values.

Solution 1Recommended

Use an IF Formula to Hide Zero Dates in Excel

Replace a direct cell reference with an IF formula that displays an empty string for blank or zero source values.

1
Select the destination cell

On the COST sheet, select the date cell that currently references the Master sheet.

2
Enter the IF formula

In the formula bar, enter =IF(MASTER!B4=0,"",MASTER!B4), replacing MASTER!B4 with the appropriate source sheet and cell, then press Enter.

3
Fill the formula down

Drag the fill handle from the formula cell down the column to apply the formula to corresponding source rows.

4
Set the date format

Select the destination cells, right-click, and choose Format Cells. On the Number tab, select Date and choose the required format.

Use an IF Formula to Hide Zero Dates in Excel
The cells are visually blank: The formula returns an empty string rather than making the destination cell truly empty. A source value of zero is hidden as well.
WPS Spreadsheet

Hide Zero Dates with an IF Formula in WPS Spreadsheet

Use the same IF formula in WPS Spreadsheet to keep linked date cells visually blank when the source is empty or zero.

  1. 1. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
  2. 2. Enter the formula: Select the destination date cell and enter =IF(MASTER!B4=0,"",MASTER!B4), adjusting the sheet and cell reference as needed.
  3. 3. Fill the column: Drag the cell's fill handle down to apply the formula to the remaining rows.
  4. 4. Format the dates: Select the destination cells, right-click, choose Format Cells, and select a Date format.
Hide zero dates while displaying valid linked dates.Open and save Microsoft Excel XLSX files.Fill formulas down a column to update corresponding row references.Choose a familiar date format for the destination cells.
microsoft office alternative - wps office

Frequently Asked Questions

Why does a blank source cell show a date in Excel?

A direct reference can evaluate an empty source cell as zero. When the destination is formatted as a date, Excel may display that zero as a date such as January 0, 1900.

How do I adapt the formula to a different source cell?

Replace both instances of MASTER!B4 with the source reference. For example, use =IF('Master'!B22=0,"",'Master'!B22) for cell B22 on the Master sheet.

Is the destination cell actually empty when the formula returns an empty string?

No. The cell still contains a formula; it only appears blank because the formula returns an empty string.

Will the formula also hide a source value of zero?

Yes. The condition sourcecell=0 hides both a blank source cell and a source value of zero.