Formula Errors

How to Prevent MAX from Returning 1/0/1900 for Blank Date Cells in Excel

Ayan Masood Oct 15, 2026 870 views

Question details

The user wants to stop the MAX function from displaying the date 1/0/1900 when calculating the latest date from a range of empty cells.

How to Prevent MAX from Returning 1/0/1900 for Blank Date Cells in Excel

Product: Spreadsheet
Device & OS: not provided
Scenario: Attempting to extract the maximum date from a specific range or set of cells that may sometimes be completely blank.
Observed behavior: When all referenced cells are blank, the MAX function evaluates them as zero. Because the result cell is formatted as a date, Excel displays this zero value as 1/0/1900.

Before you start

Identify the exact cell references or range your MAX formula is targeting, and confirm that the destination cell is properly formatted as a Date.

Solution 1Recommended

Combine MAX with IF and COUNT Functions

This method checks if there are any numeric values in the referenced cells. If none exist, it returns an empty string instead of executing the MAX function.

The COUNT function only counts cells containing numbers (and dates are stored as numbers). By placing it inside an IF statement, we can ensure the MAX function only runs when there is at least one valid date present.

Select the result cell

Click on the cell where you want the latest date to be displayed.

Enter the IF and COUNT formula

Type the formula =IF(COUNT(A1,F1,I1)=0,"",MAX(A1,F1,I1)), making sure to replace A1, F1, and I1 with your actual cell references or ranges.

Apply the formula

Press Enter. The cell will now remain perfectly blank if all the referenced date cells are empty, preventing the 1/0/1900 error.

Using a Continuous Range: If your dates are in a continuous block, you can simply use a range instead of individual cells, like this: =IF(COUNT(A1:A10)=0,"",MAX(A1:A10)).

Solution 2

Use IF with OR and ISNUMBER for Non-Contiguous Cells

This approach explicitly checks each individual cell to see if it contains a valid numeric date before executing the MAX calculation.

WPS Spreadsheet Solution

Handle Complex Date Formulas Easily with WPS Spreadsheet

WPS Spreadsheet provides a robust and user-friendly environment for managing complex formulas like IF, COUNT, and MAX. It handles date errors seamlessly and formats data exactly as you need.

1. Open your spreadsheet: Launch WPS Office and open your .xlsx file using the WPS Spreadsheet module.
2. Select the target cell: Click on the cell where you want to calculate the maximum date.
3. Enter the conditional formula: Type =IF(COUNT(A1:A5)=0,"",MAX(A1:A5)) in the formula bar.
4. View the correct result: Press Enter to execute. The cell will intelligently display either the maximum date or remain completely blank.

Full compatibility with Microsoft Excel formulas and .xlsx file formatsBuilt-in formula error checking to easily spot and fix formatting issuesFree and lightweight software for smooth, lag-free data analysisFamiliar user interface requiring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel show 1/0/1900 for zero values?

Spreadsheet software stores dates as sequential serial numbers starting from January 1, 1900, which is represented by the number 1. Therefore, the number 0 translates to the theoretical date of January 0, 1900. When a cell with a value of 0 is formatted as a date, it displays as 1/0/1900.

How do I globally hide all zero values in an Excel worksheet?

You can hide zeros by going to File > Options > Advanced. Scroll down to the 'Display options for this worksheet' section, and uncheck the box that says 'Show a zero in cells that have zero value'. Note that this will affect the entire sheet, not just your date calculations.

Can I use custom number formatting to hide the 1/0/1900 date?

Yes. You can bypass complex IF formulas by changing the cell's format. Press Ctrl+1 to open Format Cells, choose 'Custom', and enter m/d/yyyy;;;@ as the type. This specific format hides zero values completely while displaying valid dates and text normally.

Excel & Data Problems