How to Fix Excel Showing 01/00/1900 for Blank Dates in Formulas
Question details
The user needs to display "N/A" or leave the space blank instead of showing the zero-date value "01/00/1900" when using CONCAT and TEXT formulas on empty date cells.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Combining or formatting referenced date cells that might be empty using CONCAT and TEXT formulas.
- Observed behavior
- Blank referenced cells are incorrectly treated as a zero value and formatted as the default zero-date, which displays as 01/00/1900.
Verify that your source date cells are truly blank and do not contain hidden space characters or hidden zero values.
Use the IF Function Inside the TEXT Formula
Wrap your cell reference in an IF function to test for blank cells before applying the TEXT format.
By default, Excel and WPS Spreadsheet treat empty cells as zero. Since dates are stored as serial numbers starting from January 1, 1900, a zero value is displayed as 01/00/1900. By adding a logical test, you can intercept the blank cell and force it to display "N/A" or text of your choosing.
Click on the cell where you want your concatenated text and dates to appear.
Instead of referencing the date cell directly like TEXT(F219, "mm/dd/yyyy"), use an IF function to check its value: IF(F219, F219, "N/A").
Nest the IF statement inside your TEXT formula. Your complete formula will look similar to: =CONCAT(TEXT(IF(F219, F219, "N/A"), "mm/dd/yyyy")).
Press Enter. If cell F219 is blank, the formula will now output "N/A" instead of formatting a zero as 01/00/1900.
Use Custom Number Formatting to Hide Zero Dates
If you are only referencing a single cell and not combining text, you can hide the 1900 date using custom number formats.
Prevent Formula Errors Seamlessly with WPS Spreadsheet
WPS Spreadsheet offers a complete and robust set of functions, including IF, TEXT, and CONCAT, helping you handle complex data combinations and avoid frustrating formatting errors like the 1900 blank date issue.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your date references.
- 2. Select the formula cell: Click on the target cell where the combined text string should be generated.
- 3. Input the dynamic formula: Type your formula using the IF function to catch blanks: =CONCAT(TEXT(IF(A2, A2, "N/A"), "mm/dd/yyyy")).
- 4. Execute and copy: Press Enter to execute the formula, then click and drag the fill handle at the bottom right of the cell to apply it to the rest of your column.
Frequently Asked Questions
Why does Excel show 1900 for blank dates?
Spreadsheet software stores dates as sequential serial numbers starting from January 1, 1900. When a formula references a blank cell, it evaluates that blank as a 0. Formatting 0 as a date returns the "zeroth" day of 1900, which displays as 01/00/1900.
Can I display a blank space instead of N/A in the formula?
Yes. In the IF function, simply replace "N/A" with a pair of empty quotation marks (""). For example: =TEXT(IF(A1, A1, ""), "mm/dd/yyyy"). This will leave the section completely blank when the referenced cell is empty.
Does this IF function solution work in both Excel and WPS Spreadsheet?
Yes, the IF, TEXT, and CONCAT functions are standard spreadsheet functions. The syntax is identical and fully supported in both Microsoft Excel and WPS Spreadsheet without any modifications.
