Excel Formula to Find the Latest Date in Every Fourth Column
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.

- 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.
Ensure your dataset contains valid date values rather than text strings, so the MONTH and DAY functions can accurately process the information.
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.
Click on the cell where you want the latest date result to be displayed.
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)
Press Enter to calculate the result. Make sure the cell is formatted as a Date so the output displays correctly.

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. Download and Install: Download WPS Office for free and install it on your device.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing Excel workbook.
- 3. Apply Complex Formulas: Paste your AGGREGATE and MOD formulas directly into the formula bar. WPS Spreadsheet will calculate the results instantly.

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.




