The dataset we’ll use for this tutorial has nine rows and three columns. Initially, we’ll keep all the cells in General format and the date values in Date format. For all the datasets, we have three unique columns which are Order Type, Order ID, and Delivery Date. However, we may vary the number of columns later if that is needed.
Method 1 – Use Excel’s MAXIFS Function to Find Max Date in Range with Criteria
Steps:
- Go to cell D12 and insert the following formula:
=MAXIFS(D5:D11,B5:B11,B5)- Press the Enter key to see the maximum date of purchase order delivery.
Read More: VLOOKUP Date Range and Return Value in Excel
Method 2 – Combine MAX and IF Functions
Steps:
- Navigate to cell D12 and type in the formula below:
=MAX(IF(B5:B11=B5,D5:D11))- Press Enter to confirm the formula.
- This should filter out the last date of delivery for the purchase orders.
Read More: How to Use IF Formula for Date Range in Excel
Method 3 – Join MAX and FILTER Functions
Steps:
- Select cell D12 and enter this formula:
=MAX(FILTER(D5:D11,B5:B11=B5))- Press Enter to get the maximum date value from the range of input dates.
Download Practice Workbook
You can download the practice workbook from here.
Related Articles
- How to Pull Data from a Date Range in Excel
- How to Use Formula for Past Due Date in Excel
- How to Calculate Average If within Date Range in Excel
<< Go Back to Date Range | Date-Time in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
