How to Prevent Excel Linked Blank Dates Showing as 1/0/1900
Question details
The user wants to prevent the 1/0/1900 date from displaying when a destination cell references a blank source cell that is formatted as a date.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Linking a destination cell to a blank source cell formatted as a date.
- Observed behavior
- Excel displays 1/0/1900 because it automatically interprets a blank reference as the date serial number zero.
Verify the exact cell references of your source data to ensure your formulas correctly point to the intended blank cells.
Use an IF Formula to Return a Blank String
This method prevents the zero value from being generated by checking if the source cell is empty before linking it.
By using the IF function, you can instruct Excel to output an empty string rather than converting a blank cell into a zero, which stops the 1/0/1900 date from appearing.
Click on the cell where you want the linked date to appear.
Type the formula: =IF(SourceCell="","",SourceCell). Replace 'SourceCell' with your actual reference, such as =IF(A2="","",A2).
Press Enter to apply the formula. If the source cell is blank, the destination cell will now appear completely empty.
Apply Custom Date Formatting
Use a custom number format to visually hide zero values while keeping the standard date format intact.
Fix Blank Date Errors Easily in WPS Spreadsheet
WPS Office provides seamless handling of date formats and formulas. You can fix the 1/0/1900 display issue using the exact same IF functions and custom formatting tools as Microsoft Excel.
- 1. Open your spreadsheet in WPS Office: Launch WPS Spreadsheet and open the file containing the linked dates.
- 2. Apply the IF Formula: Select the destination cell and enter =IF(A1="","",A1) to prevent the zero value from rendering.
- 3. Format Cells: Alternatively, right-click the cell, select 'Format Cells', and apply the custom mm/dd/yyyy;; format to hide the zero date.
Frequently Asked Questions
Why does Excel show 1/0/1900 instead of just being blank?
Excel uses a serial number system for dates, starting from January 1, 1900. A blank referenced cell automatically evaluates to zero. When Excel formats zero as a date, it corresponds to the fictional date of January 0, 1900 (1/0/1900).
Does the custom format mm/dd/yyyy;; delete the zero value?
No, the custom format only hides the zero value from being displayed on the screen. The underlying cell value remains zero, which means it can still be referenced as a zero in other mathematical calculations.
How do I apply the IF formula across different sheets?
You can include the sheet name in the cell reference. For example, to reference cell A1 on Sheet1, use the formula: =IF(Sheet1!A1="","",Sheet1!A1).
Will these solutions work if the date format is dd/mm/yyyy?
Yes. If your regional settings use dd/mm/yyyy, the IF formula works exactly the same. For the custom formatting method, simply adjust the code to dd/mm/yyyy;; instead.
