Keep Excel Date Cells Blank When the Source Is Zero
Question details
Keep linked date cells visually blank when their source cells are empty or contain 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.
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.
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.
On the COST sheet, select the date cell that currently references the Master sheet.
In the formula bar, enter =IF(MASTER!B4=0,"",MASTER!B4), replacing MASTER!B4 with the appropriate source sheet and cell, then press Enter.
Drag the fill handle from the formula cell down the column to apply the formula to corresponding source rows.
Select the destination cells, right-click, and choose Format Cells. On the Number tab, select Date and choose the required format.

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. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
- 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. Fill the column: Drag the cell's fill handle down to apply the formula to the remaining rows.
- 4. Format the dates: Select the destination cells, right-click, choose Format Cells, and select a Date format.

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.




