'%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 Show a Blank Instead of 12:00 AM in Excel XLOOKUP
Question details
The user wants to display an empty cell instead of "12:00 AM" when an XLOOKUP formula retrieves a blank date cell from the source data.
- Product
- Microsoft Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Using the XLOOKUP function to extract date values from a raw data sheet into another table.
- Observed behavior
- When the referenced source cell is completely blank, the XLOOKUP function returns a 0. Because the destination cell is formatted as a date, Excel converts and displays this 0 as "12:00:00 AM" instead of remaining empty.
Verify that your destination cell is properly formatted as a Date, and ensure that the blank cells in your source data do not accidentally contain hidden space characters.
Wrap XLOOKUP with an IF Function
This method evaluates if the XLOOKUP result is 0 (a blank source cell) and uses the IF function to force it to output an empty text string instead.
When XLOOKUP points to an empty cell, Excel defaults to returning a 0 value. If your destination cell has Date or Time formatting applied, Excel interprets that 0 as January 0, 1900, which displays as 12:00 AM. Wrapping your XLOOKUP formula in a logical IF statement intercepts this 0 and outputs a blank space before it gets formatted as a date.
Click on the cell where you want to output your looked-up date.
Type the formula using this structure: =IF(XLOOKUP(lookup_value, lookup_array, return_array, "")=0, "", XLOOKUP(lookup_value, lookup_array, return_array, "")). For example, enter =IF(XLOOKUP([@[GRACE ID]],'WFP Raw data'!B:B,'WFP Raw data'!O:O,"")=0,"",XLOOKUP([@[GRACE ID]],'WFP Raw data'!B:B,'WFP Raw data'!O:O,"")).
Press Enter. The cell will now check if the lookup evaluates to 0. If it does, it returns an empty string (""); otherwise, it displays the actual date retrieved by the second XLOOKUP.
Hide Zero Values Using Custom Number Formatting
If you prefer not to write a long double-lookup formula, you can use custom cell formatting to simply hide the zero values from displaying as 12:00 AM.
Effortlessly Manage Complex Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array functions like XLOOKUP, IF, and LET, helping you analyze and structure your data seamlessly. Solve zero-value display issues and other formatting hurdles in seconds.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file where you are performing the data lookup.
- 2. Select the formula cell: Click the cell where you want to apply the XLOOKUP function.
- 3. Input the nested IF function: Type the formula exactly as =IF(XLOOKUP(...)=0, "", XLOOKUP(...)) to intercept the blank source values.
- 4. Press Enter to execute: Press Enter, and the zero-date result will be instantly replaced by a clean, empty string.
Frequently Asked Questions
Why does Excel show 12:00 AM instead of a blank cell in lookups?
When a formula points to an empty cell, Excel defaults to returning a numerical 0 instead of a null or blank value. If your destination cell has a Date or Time format applied, Excel translates that 0 into the system's baseline starting date (typically January 0, 1900, 12:00:00 AM).
Can I use IFERROR instead of IF to hide the 12:00 AM result?
No. The IFERROR function only catches explicit calculation errors like #N/A, #VALUE!, or #REF!. Because returning a 0 from a blank source cell is considered a valid mathematical result by Excel, IFERROR will not trigger. You must use the IF function to specifically evaluate if the result is 0.
What happens if my lookup result is legitimately 0 and not just a blank cell?
The IF formula =IF(XLOOKUP(...)=0, "", ...) cannot differentiate between an actual mathematical 0 and a blank cell that Excel interpreted as a 0. Both will be converted to a blank string. If your dataset legitimately contains a '0' that you need to retain, you would need to use a more complex logic, like combining XLOOKUP with the ISBLANK function.
Is the XLOOKUP function available in WPS Spreadsheet?
Yes, WPS Spreadsheet fully supports the XLOOKUP function along with its optional parameters like if_not_found, match_mode, and search_mode. You can directly copy and paste your Excel XLOOKUP and IF formulas into WPS Spreadsheet and they will work seamlessly.
