Formula Errors

How to Mark the Latest Date in Each Month in Excel

Guest Writer Oct 1, 2026 869 views

Question details

Identify and mark the row containing the maximum (latest) date for each distinct month within a dataset.

How to Mark the Latest Date in Each Month Using Excel Formulas

Product: Excel
Device & OS: not provided
Scenario: When tracking records, sales, or logs over a long period, users need to single out the final recorded date for each calendar month to perform end-of-month calculations or summaries.
Observed behavior: A specific character, such as an asterisk (*), is placed in a helper column immediately next to the latest date corresponding to each month.

Before you start

Ensure your dates are formatted as actual serial dates in your spreadsheet, not as plain text strings, so that the MAXIFS and EOMONTH functions can calculate the time values correctly.

Solution 1Recommended

Use the MAXIFS and EOMONTH Functions

This is the most reliable method for most versions, utilizing MAXIFS to find the highest date bounded by the start and end of the current month.

The MAXIFS function evaluates the maximum value based on specific conditions. Combined with EOMONTH, it dynamically sets the boundary for the first and last day of the month for every date evaluated in the column.

Select a helper cell

Click on the first cell in a blank adjacent column (for example, C2) assuming your dates are located in the range B2:B28.

Enter the MAXIFS formula

Input the following formula: =IF(B2=MAXIFS($B$2:$B$28,$B$2:$B$28,">="&EOMONTH(B2,-1)+1,$B$2:$B$28,"<="&EOMONTH(B2,0)),"*","")

Apply the formula to the column

Press Enter to apply the formula. Then, click and drag the fill handle at the bottom-right of cell C2 downwards to fill the formula through to row 28.

Expected Result: An asterisk (*) will immediately appear in the helper column next to the highest date found for each month.

Solution 2

Use Dynamic Array Formulas (FILTER and MAX)

For newer versions that support dynamic arrays, the FILTER function offers a highly readable alternative using the TEXT function.

Easily Manage Advanced Date Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like MAXIFS, EOMONTH, and dynamic arrays, making it effortless to analyze and manipulate date-based datasets without encountering compatibility errors.

1. Open your workbook: Launch WPS Spreadsheet and open your existing .xlsx workbook containing the dates.
2. Paste the formula: Click on the target cell and paste the MAXIFS or FILTER formula directly into the formula bar.
3. Auto-fill the column: Double-click the fill handle on the bottom-right corner of the cell to automatically apply the calculation to your entire date column.

Fully compatible with Microsoft Excel formulas and .xlsx file formatsSupports advanced dynamic array functions like FILTERLightweight, fast, and completely free to use for everyday tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why is my MAXIFS formula returning an error or missing the asterisk?

This usually happens if your dates are formatted as text rather than valid serial dates. Select your dates, navigate to the Data tab, and use the 'Text to Columns' feature to quickly convert them to a valid Date format.

Can I highlight the entire row instead of just adding an asterisk mark?

Yes. You can copy the conditional part of the formula (e.g., =$B2=MAXIFS(...)) and paste it into the Conditional Formatting tool under 'New Rule' > 'Use a formula to determine which cells to format', then select a fill color.

Does the EOMONTH function work the same way in WPS Spreadsheet?

Yes, WPS Spreadsheet has complete built-in support for EOMONTH, MAXIFS, and other advanced date-time functions, ensuring 100% logical compatibility with files created in Excel.

Excel & Data Problems