How to Return a Blank Instead of 1/0/1900 in WPS Office Spreadsheets Date Formulas
Question details
The user needs a WPS Office Spreadsheets formula or formatting method to display a blank cell instead of 1/0/1900 when a date lookup result is zero or empty.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A date lookup formula such as VLOOKUP is being used in a workbook, and some source date cells are blank.
- Observed behavior
- The formula displays 1/0/1900 because Excel-compatible spreadsheets treat an empty date as serial number zero.
Open the workbook in WPS Office Spreadsheets and identify the formula cells that currently show 1/0/1900. Confirm which source date cells can be empty so the formula can be adjusted accurately.
Use IF and VLOOKUP to Return a Blank for Zero Dates
Best when you need the cell value to truly remain blank instead of only hiding the display.
WPS Office Spreadsheets stores dates as serial numbers. An empty date cell returns 0, and date formatting shows 0 as 1/0/1900. Wrapping the lookup in an IF test returns an empty string whenever the lookup result is zero.
Click the first cell that displays 1/0/1900 and put the cursor in the formula bar.
Enter =IF(VLOOKUP(A2,Table,2,FALSE)=0,"",VLOOKUP(A2,Table,2,FALSE)) and adjust A2 and Table to match your lookup value and range.
Press Enter, then drag the fill handle down or double-click it to apply the formula to the remaining cells.
Hide 1/0/1900 with a Custom Date Format
Use when you prefer not to change the formula and only need the zero date hidden visually.
Return Blank Values in WPS Office Spreadsheets Date Lookups
WPS Office Spreadsheets can complete this directly with the same Excel-compatible formulas and custom formats. Wrap a VLOOKUP in IF to return a blank value whenever the source date is empty, then copy the formula through the column.
- 1. Open your workbook: Launch WPS Office Spreadsheets, click Open, and select the workbook that contains the 1/0/1900 date formula.
- 2. Edit the formula: Click the cell that shows 1/0/1900 and enter =IF(VLOOKUP(A2,Table,2,FALSE)=0,"",VLOOKUP(A2,Table,2,FALSE)) in the formula bar.
- 3. Copy the formula: Press Enter and drag the fill handle down to apply the corrected formula to all affected date rows.
- 4. Save in Excel format: Click Menu > Save As and choose .xlsx to keep the workbook compatible with Microsoft Excel and WPS Office.
Frequently Asked Questions
Why does a blank source date show as 1/0/1900 in a VLOOKUP formula?
WPS Office Spreadsheets, like Excel, stores dates as serial numbers. A blank date cell returns 0, and when number format is a date, 0 is displayed as 1/0/1900.
How do I return a true blank instead of 1/0/1900 in WPS Office?
Use =IF(VLOOKUP(A2,Table,2,FALSE)=0,"",VLOOKUP(A2,Table,2,FALSE)). Adjust A2 and Table to your lookup value and range.
Can I hide 1/0/1900 without changing my formula?
Yes. Select the cells, press Ctrl+1, click Custom, and enter mm/dd/yyyy;;. The zero date is hidden visually, but the cell still contains 0.
Is this formula compatible with Microsoft Excel?
Yes. WPS Office Spreadsheets supports Excel-style formulas and .xlsx files, so the same IF and VLOOKUP formula works in both applications.
