'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Stop Excel from Displaying 1/0/1900 for Blank Dates
Question details
The user wants to prevent Excel from displaying the date '1/0/1900' when a formula returns zero or references a blank cell formatted as a date.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Using a formula (like MAX or VLOOKUP) to retrieve date values where the source data might be empty or missing, resulting in unwanted 1/0/1900 values.
- Observed behavior
- When the formula references a blank cell, it calculates a value of 0. Because the destination cell is formatted as a date, Excel interprets 0 as January 0, 1900, displaying '1/0/1900' instead of remaining blank.
Identify which formula or cell reference is pulling the blank data so you can modify its logic to explicitly handle empty inputs.
Use an IF Function to Return an Empty String
Modify your existing formula by wrapping it in an IF statement to check for blank cells, ensuring it returns an empty string rather than a zero.
This is the most precise method as it directly targets the formula causing the issue without affecting the display of actual zero values elsewhere in your worksheet.
Click on the cell that is incorrectly displaying the 1/0/1900 date.
Click into the Formula Bar at the top of the worksheet to modify your existing formula.
Wrap your formula in an IF function to check if the input cell is blank. For example, change =MAX(...) to =IF(B8="","", MAX(...)). For more complex arrays, you might use: =IFERROR(IF(B8="","",MAX(IF(B8='Chat Record'!$C$3:$C$9999,'Chat Record'!$D$3:$D$9999))),"").
Press Enter to save the formula. The cell will now appear completely blank if the source data is missing.
Hide Zero Values in Worksheet Options
If you do not want to rewrite multiple formulas, you can configure Excel settings to hide all zero values in the current worksheet.
Apply Custom Number Formatting
Format the specific cells so that zero values are suppressed and hidden, leaving the rest of the worksheet unaffected.
Manage Spreadsheet Data Effectively with WPS Office
WPS Spreadsheet offers robust formula capabilities and seamless compatibility with Microsoft Excel, making it incredibly easy to troubleshoot date errors like '1/0/1900' and process complex data accurately.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the date errors.
- 2. Locate the formula: Select the cell displaying 1/0/1900 and click into the formula bar.
- 3. Update the logic: Insert an IF function to handle blanks, such as =IF(A1="","", A1).
- 4. Apply and drag: Press Enter, then drag the fill handle to apply this corrected formula to the rest of the column.
Frequently Asked Questions
Why does Excel show 1/0/1900 instead of just remaining blank?
Excel stores dates as sequential serial numbers, starting with 1 for January 1, 1900. When a formula points to a blank cell, it evaluates it as 0. Because the destination cell is formatted to display a date, Excel interprets that 0 as the 'zeroth' day of 1900, displaying it as 1/0/1900.
Can I use conditional formatting to hide the 1/0/1900 date?
Yes. You can create a conditional formatting rule for the affected cells where the cell value is equal to 0. Set the font color in the formatting rule to match the cell's background color (typically white). This will make the 1/0/1900 date invisible to the user.
Will hiding zero values via Excel Options affect my other formulas?
Yes. If you disable the 'Show a zero in cells that have zero value' setting, it hides zeros across the entire worksheet. If you need to see actual zero values in financial calculations or inventory counts on the same sheet, it is safer to use the IF formula method or Custom Number Formatting instead.
