How to Mark the Most Recent Date for Each Customer as Open in Excel
Question details
The user needs a way to evaluate a dataset and automatically label the row with the latest date for each specific customer code as "Open", while marking all older records for that customer as "Close".
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Managing customer records over time and needing to quickly identify the most recent interaction, transaction, or status for each unique customer.
- Observed behavior
- The goal state is to have a dynamic column that automatically updates to display "Open" next to the newest date for a given customer code and "Close" for all past dates.
Ensure your data is formatted as an Excel Table (press Ctrl+T) and that your date column contains valid Excel date values rather than plain text. This ensures the MAXIFS function can accurately compare and find the latest chronological date.
Use HSTACK, IF, and MAXIFS Dynamic Array Formula
This method uses modern dynamic array functions to generate a complete copy of your table with the new status column appended automatically.
This solution relies on the MAXIFS function to look through your data and return the maximum value (latest date) that meets your specified criteria (the customer code). Combining this with an IF statement evaluates the dates, and HSTACK appends the final result directly to your dataset as a dynamic array.
Select your entire dataset, press Ctrl+T, and name your table "Table" in the Table Design tab. Ensure your columns are named exactly "Date" and "Customer Code".
Click on a blank cell in your worksheet where you want the new expanded table to generate. Make sure there is enough empty space to the right and below.
Type the formula: =HSTACK(Table,IF(Table[Date]=MAXIFS(Table[Date],Table[Customer Code],Table[Customer Code]),"Open","Close")) and press Enter. The entire table will spill into the new area with the final column displaying the correct status.
Add a Standard Status Column using MAXIFS
A traditional approach if you want to add the status directly inside your existing data table without generating a whole new array via HSTACK.
Use WPS Spreadsheet to Manage Customer Records Efficiently
WPS Spreadsheet fully supports advanced logical and mathematical functions like MAXIFS, IF, and modern dynamic arrays, making it incredibly easy to track customer statuses and analyze chronological data without compatibility issues.
- 1. Open your dataset: Launch WPS Spreadsheet and open your customer records workbook.
- 2. Format as a Table: Highlight your data range and navigate to Insert > Table to convert it into a structured table.
- 3. Apply the formula: Add a new column and input the =IF([@Date]=MAXIFS([Date],[Customer Code],[@[Customer Code]]),"Open","Close") formula to identify the latest records instantly.
Frequently Asked Questions
Why does MAXIFS return an error or 0 for my dates?
This usually happens if your dates are stored as text instead of actual numerical date values. Select your date column, go to the Data tab, and use 'Text to Columns' to convert them, or change the cell format to Short Date.
What happens if a customer has two identical recent dates?
If there is a tie for the most recent date, the MAXIFS function will return that same date for both rows. The IF statement will evaluate both as true, meaning both rows for that customer will be marked as "Open".
Can I mark the most recent date without creating an Excel Table?
Yes, you can use standard absolute cell references instead of structured table references. For example, use =IF(B2=MAXIFS($B$2:$B$100, $A$2:$A$100, A2), "Open", "Close") where column A is the Customer Code and B is the Date. Remember to lock the evaluation ranges with dollar signs.
