How to Hide 1/0/1900 in Excel When Referencing a Blank Cell
Question details
The user needs to hide or remove the 1/0/1900 date value that appears when a date-formatted cell references an empty cell.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- A user is referencing a blank cell within another cell that is formatted as a date.
- Observed behavior
- Excel displays 1/0/1900 because it interprets the referenced blank cell as the serial date zero.
Determine whether you need the target cell to be completely blank for future calculations or if simply hiding the zero value visually is sufficient for your spreadsheet.
Use the IF Function to Return a Blank
The most reliable method is using an IF formula to check if the source cell is blank before returning its value.
By wrapping your cell reference in an IF function, you can instruct the spreadsheet to output a completely empty string instead of processing a zero value.
Click on the cell where you want the referenced date to appear.
Type the formula =IF(A1="","",A1) into the formula bar, replacing A1 with the actual cell reference you are pulling data from.
Press Enter. If the source cell is empty, your new cell will now remain blank instead of displaying 1/0/1900.
Drag the fill handle (the small square at the bottom-right of the selected cell) down to apply this formula to the rest of your column.
Apply a Custom Number Format
You can modify the cell's formatting to visually hide zero values without changing the underlying cell data.
Fix Date Errors Effortlessly in WPS Spreadsheet
WPS Spreadsheet is a powerful, free alternative that fully supports standard formulas, including IF functions and complex custom number formatting, helping you clean up data errors like 1/0/1900 seamlessly.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your .xlsx workbook containing the date references.
- 2. Apply the IF formula: Select the target cell and type =IF(A1="","",A1) to instantly replace 1/0/1900 with a blank cell.
- 3. Alternative: Use custom formatting: Press Ctrl+1 to open the cell properties, select Custom formatting, and apply your preferred zero-hiding format.
Frequently Asked Questions
Why does Excel show 1/0/1900 for blank cells?
Spreadsheet programs like Excel and WPS Office store dates as sequential serial numbers starting from January 1, 1900. An empty cell evaluates to the number 0. When that 0 is formatted as a date, it displays as the starting point of the date system: 1/0/1900.
Can I hide all zero values in the entire worksheet at once?
Yes. You can go to File > Options > Advanced. Scroll down to the 'Display options for this worksheet' section and uncheck 'Show a zero in cells that have zero value'. Note that this will hide zeros in all cells, not just those formatted as dates.
Will hiding the 1/0/1900 date affect my calculations?
It depends on the method used. If you use the IF function to return a blank text string (""), some math operations referencing that cell might return a #VALUE! error. If you use custom formatting, the underlying value remains 0, which can still be calculated normally.
