How to Prevent Excel FILTER from Displaying Zero Date 00/01/1900
Question details
The user wants to remove or hide the zero date value (00/01/1900) that automatically appears when using the FILTER function to extract data that includes blank date cells.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Using the dynamic FILTER function to pull records from a dataset where the date column contains some empty cells.
- Observed behavior
- Blank date cells from the source array are evaluated as zero. When the resulting cell is formatted as a date, this zero serial number displays as 00/01/1900 or 1/0/1900.
Ensure your source data range is correct and identify the exact column containing the dates that are returning the 00/01/1900 error.
Add a Positive Value Condition to the FILTER Formula
Prevent invalid dates from entering the dynamic array result entirely by filtering out cells where the date value is zero or blank.
This is the most robust method because it tackles the data at the formula level. By explicitly telling the FILTER function to only return rows where the date value is greater than zero, the blank cells are completely excluded from your final array.
Check your original dataset to see which column contains the dates. For example, assume your dates are in column E from E2 to E40000.
Add a new condition to your existing FILTER formula. Multiply your current criteria by the condition (E2:E40000>0). The asterisk (*) acts as an AND operator in array formulas.
Enter your complete formula into the cell. For example: =IF(B1="","",SORT(FILTER(B2:E40000,(ISNUMBER(SEARCH($B$1,B2:B40000)))*(E2:E40000>0),""),3,1)) and press Enter.
Hide Zero Dates Using Custom Number Formatting
Keep the underlying zero values in the dataset but apply a custom format rule so that 00/01/1900 becomes invisible.
Handle Dynamic Array Formulas Seamlessly with WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like FILTER, SORT, and IF. You can easily build complex data extraction models, apply multiple logical criteria, and manage date formats without software lag or zero-date errors.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your raw data.
- 2. Enter the dynamic formula: Type the FILTER formula using the greater-than-zero condition (e.g., *(E2:E40000>0)) to exclude empty date cells.
- 3. Press Enter to spill results: Hit Enter. WPS Spreadsheet will instantly spill the clean, correctly formatted results into the adjacent cells.
Frequently Asked Questions
Why does my spreadsheet show 00/01/1900 for blank cells?
Spreadsheets store dates as sequential serial numbers starting from January 1, 1900. An empty cell evaluates to the number 0. When a cell with the value 0 is formatted as a date, it displays the starting point of this system: 00/01/1900 (or 1/0/1900).
Can I use the IF function to hide the zero date instead?
Yes. You can wrap your formula in an IF statement to check for zeros, such as =IF(FILTER(...)="","",FILTER(...)). However, doing this with dynamic arrays forces the software to calculate the FILTER function twice, which can slow down performance on large datasets. Appending the >0 condition directly inside the FILTER criteria is much more efficient.
Does this zero-date issue happen with VLOOKUP or XLOOKUP?
Yes, lookup functions like VLOOKUP, HLOOKUP, XLOOKUP, and INDEX/MATCH will also return a 0 if the target cell is blank. If the cell you are pulling data into is formatted as a date, it will display 00/01/1900. You can use the same custom number format (mm/dd/yyyy;;) to hide these results.
