Formula Errors

Excel Formula to Find the Latest Date in Every Fourth Column

Phi Hung VoPhi Hung Vo Nov 11, 2026 870 views

Question details

The user needs a formula to extract the latest date based on the month and day, specifically evaluating only the dates located in every fourth column of a given range.

How to Find the Latest Date in a Specific Column Range in Excel
Product
Excel / WPS Spreadsheet
Device & OS
not provided
Scenario
Analyzing a periodic dataset where dates are spaced out across columns (e.g., every 4th column) and extracting the maximum date value without evaluating irrelevant columns in between.
Observed behavior
A standard MAX function evaluates the entire range, but the user requires a specialized formula to filter the evaluation to specific interval columns and calculate based purely on month and day values.
Before you start

Ensure your dataset contains valid date values rather than text strings, so the MONTH and DAY functions can accurately process the information.

Solution 1Recommended

Use AGGREGATE with MOD to Extract the Latest Date

Combine the AGGREGATE function to find the maximum value while ignoring errors, and the MOD function to filter every fourth column.

To evaluate every Nth column without using volatile array formulas or helper rows, you can use the AGGREGATE function. AGGREGATE with function number 14 acts like a LARGE function, and option 6 tells it to ignore errors.

By dividing the date calculation by a MOD equation that forces a division-by-zero error on non-target columns, the AGGREGATE function naturally filters out any column that isn't the fourth one in the sequence.

1
Select the destination cell

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

2
Enter the AGGREGATE formula

Type the following formula: =AGGREGATE(14,6,$D3:$AQ3/((MONTH($D3:$AQ3)*100+DAY($D3:$AQ3))=AGGREGATE(14,6,(MONTH($D3:$AQ3)*100+DAY($D3:$AQ3))/(MOD(COLUMN($D3:$AQ3)-COLUMN($D3)+1,4)=0),1)),1)

3
Apply the formula

Press Enter to calculate the result. Make sure the cell is formatted as a Date so the output displays correctly.

Use AGGREGATE with MOD to Extract the Latest Date
Formula Breakdown: The expression (MONTH*100+DAY) creates a 4-digit number (e.g., December 31 becomes 1231) to easily determine the highest month and day. The MOD(...,4)=0 segment ensures only every 4th column is processed.
Advanced Data Analysis

Easily Manage Complex Array Formulas in WPS Spreadsheet

WPS Spreadsheet provides robust support for advanced data manipulation, including complex array formulas using AGGREGATE, MOD, and date functions. It seamlessly processes intricate logical evaluations across large datasets.

  1. 1. Download and Install: Download WPS Office for free and install it on your device.
  2. 2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing Excel workbook.
  3. 3. Apply Complex Formulas: Paste your AGGREGATE and MOD formulas directly into the formula bar. WPS Spreadsheet will calculate the results instantly.
Fully compatible with Microsoft Excel formulas and formatting.Free, lightweight, and fast performance on large datasets.Built-in formula auditing tools to help troubleshoot complex nested functions.
QA img-9

Frequently Asked Questions

Why does my formula return a #NUM! or #VALUE! error?

This usually happens if the selected range contains text values or empty cells that cannot be parsed by the MONTH or DAY functions. Ensure all evaluated cells in the target columns contain proper date formats.

Can I modify the formula to look at every third column instead of the fourth?

Yes. You can change the interval by updating the divisor in the MOD function. Simply change the '4' to a '3' in this section: MOD(COLUMN($D3:$AQ3)-COLUMN($D3)+1,3)=0.

How does the AGGREGATE function differ from the MAX function in this scenario?

The AGGREGATE function allows you to force and subsequently ignore errors (using option 6). This is essential for this technique, as we intentionally divide by zero to exclude irrelevant columns, which the standard MAX function cannot handle natively.

Why multiply the month by 100 in the formula?

Multiplying the month by 100 and adding the day creates a sortable numeric value. For example, October 15 becomes 1015, and March 5 becomes 305. This allows the formula to easily find the latest date based purely on the month and day without factoring in the year.