Function Problems

WPS Spreadsheet: Return a Blank Result When No Dates Exist

Khadija Khan Nov 12, 2026 869 views

Question details

The user needs a formula that returns a blank cell when none of the referenced cells contains a date.

Product: WPS Spreadsheet
Device & OS: not provided
Scenario: Several nonadjacent cells are checked with MAX to find the latest date, but the result should remain blank when all referenced cells are empty.
Observed behavior: MAX returns the date serial 0, which displays as 1/0/1900, when all referenced cells are blank.

Before you start

Confirm that the referenced cells contain real spreadsheet date values rather than dates stored as text. Identify every cell that should be included in the COUNT and MAX checks before entering the formula.

Solution 1Recommended

Use IF and COUNT to return a blank when no dates exist

Use this formula when the referenced cells contain actual numeric date values and the result should be blank if none of them is populated.

COUNT detects numeric date serials, while MAX returns the latest date only when at least one date is present. This prevents a zero result from being displayed as 1/0/1900.

Select the result cell

Open the worksheet in WPS Spreadsheet and click the cell where the latest date should appear.

Enter the conditional formula

Type =IF(COUNT(W2,Y2,AA2,AC2,AE2,AG2,AI2)=0,"",MAX(W2,Y2,AA2,AC2,AE2,AG2,AI2)) in the formula bar, then press Enter.

Format the result as a date

Select the result cell, open the Home tab, choose the Number Format menu, and apply a Date format so a valid maximum value displays as a date.

Expected result: The result cell stays blank when W2, Y2, AA2, AC2, AE2, AG2, and AI2 contain no numeric dates. When one or more dates exist, it shows the latest date.

Solution 2

Convert text dates before testing them

Use this approach when COUNT returns zero even though the cells appear to contain dates because the values are stored as text.

Handle empty date ranges in WPS Spreadsheet without showing 1/0/1900

WPS Spreadsheet can combine IF, COUNT, and MAX to test whether any referenced date exists, return a blank when the range is empty, and display the latest valid date when data is available.

1. Install and open WPS Office: Download WPS Office, launch it, and open the workbook in WPS Spreadsheet.
2. Choose the output cell: Click the cell intended to show the latest date.
3. Add the conditional MAX formula: Enter =IF(COUNT(W2,Y2,AA2,AC2,AE2,AG2,AI2)=0,"",MAX(W2,Y2,AA2,AC2,AE2,AG2,AI2)) in the formula bar and press Enter.
4. Apply date formatting: On the Home tab, open Number Format and select a Date format for the result cell.

Prevents empty date checks from displaying the misleading 1/0/1900 resultWorks with nonadjacent date cells in a single formulaProvides a familiar spreadsheet interface for editing and formatting formulasSupports Microsoft Excel XLS and XLSX files for smooth file compatibility

microsoft office alternative - wps office

Frequently Asked Questions

Why does MAX display 1/0/1900 when all date cells are blank?

Blank cells are treated as zero by MAX. When the result cell uses a date format, the zero serial value is displayed as 1/0/1900. Wrapping MAX in IF(COUNT(...)=0,"",MAX(...)) prevents that output.

Can I use this formula with nonadjacent cells?

Yes. COUNT and MAX accept comma-separated references such as W2,Y2,AA2,AC2,AE2,AG2,AI2, so the formula can check selected cells that are not next to each other.

What if the dates are stored as text?

COUNT does not count text values as numeric dates. Convert the cells to real dates using Data > Text to Columns, or otherwise convert the values before using the IF and COUNT formula.

How do I make the returned value display as a date in WPS Spreadsheet?

Select the formula cell, go to Home > Number Format, and choose a Date format. The formula returns a valid date serial when at least one referenced cell contains a numeric date.

Excel & Data Problems