Function Problems

How to Combine IF and MAX Formulas for Missing Dates in Excel

Olivia Miller Oct 10, 2026 869 views

Question details

The user needs an Excel formula to return the latest receipt date for a purchase order, but display 'No receipt' if no dates are found instead of returning 0 or an error.

How to Combine IF and MAX Formulas for Missing Dates in Excel

Product: Excel 2016
Device & OS: not provided
Scenario: Calculating the most recent posting or receipt date based on matching a specific purchase order while elegantly handling empty records.
Observed behavior: Standard MAX formulas return 0 (formatted as January 1, 1900) or errors when no matching receipt date exists for a given purchase order.

Before you start

Ensure that your purchase order and receipt date data are formatted in proper columns, and that your date column is actively formatted as a Date, not as Text.

Solution 1Recommended

Use an IFERROR and MAX Array Formula

This method forces a calculation error when the maximum result is zero by dividing 1 by the MAX formula, which the IFERROR function then catches to display "No receipt".

In Excel 2016, combining logical conditions with MAX requires an array formula. By converting the result of a missing date (0) into a denominator (1/0), Excel generates a #DIV/0! error. This intentionally triggers the IFERROR condition, providing an elegant workaround for displaying text instead of 1/1/1900.

Select the target cell

Click on the cell where you want to output the latest receipt date for your purchase order.

Enter the array formula

Type the formula =IFERROR(1/(1/MAX(($A$3:$A$20=D3)*$B$3:$B$20)),"No receipt"). Ensure you update the column ranges: $A$3:$A$20 represents your Purchase Order list, $B$3:$B$20 represents the Receipt Dates, and D3 is the specific order number you are checking.

Confirm as an array formula

Instead of simply pressing Enter, press Ctrl + Shift + Enter simultaneously. Excel will enclose the formula in curly brackets { }, indicating it is being calculated as an array.

Format Cells as Date: After confirming the formula, right-click the result cell, select "Format Cells," and apply a Date format to ensure valid dates are displayed correctly.

Solution 2

Use the MAXIFS Function

If you are using a newer version of Excel or WPS Spreadsheet, the MAXIFS function is a much simpler, cleaner alternative.

Solution 3

Create a Pivot Table

A Pivot Table allows you to summarize and extract the latest dates associated with purchase orders without writing complex nested formulas.

Advanced Formula Support

Calculate Complex Data Effortlessly with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas like IFERROR, MAX, and MAXIFS. You can handle missing data and complex lookups with zero compatibility issues directly within a familiar interface.

1. Open your data file in WPS Spreadsheet: Launch WPS Spreadsheet and open the .xlsx file containing your purchase orders and receipt dates.
2. Select the target cell: Click on the specific cell next to your purchase order reference where you want the latest date to appear.
3. Type the IFERROR/MAX formula: Input the formula =IFERROR(1/(1/MAX(($A$3:$A$20=D3)*$B$3:$B$20)),"No receipt") adjusted to your exact rows.
4. Calculate and fill down: Press Ctrl+Shift+Enter to complete the array calculation, then double-click the fill handle at the bottom-right of the cell to apply the formula down your entire list.

Seamless compatibility with Microsoft Excel .xlsx files and formulasFull native support for complex array formulas and modern logical functionsLightweight, fast, and completely free to download for everyday data processing

microsoft office alternative - wps office

Frequently Asked Questions

Why does my MAX formula return January 1, 1900 instead of being blank?

In spreadsheet programs, blank cells or zero values are interpreted as the serial number 0. Because date formatting starts on January 1, 1900, a result of 0 is displayed as that date. Dividing by zero forces an error, allowing you to intercept it with IFERROR.

What does pressing Ctrl+Shift+Enter do in Excel 2016?

Pressing Ctrl+Shift+Enter tells the software to evaluate the formula as an array formula. This means it processes multiple values simultaneously (checking each row against your criteria) rather than just looking at a single value. It is required for combining IF statements inside a MAX function in older versions.

Can I use the MAXIFS function in Excel 2016?

No, the MAXIFS function was introduced in Excel 2019 and is available in Microsoft 365 and WPS Office. If you are strictly using Excel 2016, you must rely on the MAX and IF array formula combination to achieve the same result.

Excel & Data Problems