How to Label Customers as New or Current Based on Date in Excel
Question details
The user needs to classify customers as 'New' or 'Current' by comparing a transaction date with the month and year of the customer's first order.

- Product
- WPS Spreadsheet / Microsoft Excel
- Device & OS
- not provided
- Scenario
- Categorizing customer sales data for reporting and cohort analysis.
- Observed behavior
- Customer statuses need to be dynamically determined via formula conditions instead of manual data entry.
Ensure your transaction dates and first-order dates are formatted as actual Date values, rather than text, to prevent formula calculation errors.
Use IF, YEAR, and MONTH Functions to Compare Dates
Extracts the year and month from both dates to verify if they fall within the exact same calendar month.
By comparing both the YEAR and the MONTH of the two dates simultaneously, you ensure that transactions happening in the same month of different years are not accidentally grouped together.
Click on the cell in your status column where you want the first customer label to appear (for example, C2).
Assuming cell A2 is the transaction date and B2 is the first-order date, type the formula: =IF(AND(YEAR(A2)=YEAR(B2), MONTH(A2)=MONTH(B2)), "New", "Current")
Press Enter to see the result. Then, click the small square at the bottom-right corner of the cell and drag it down to fill the formula for your remaining rows.
Use the EOMONTH Function for a Shorter Formula
Compares the end-of-month dates for both the transaction and first order to quickly determine the status.
Easily Classify Customer Data in WPS Spreadsheet
WPS Spreadsheet provides powerful date and logical functions to automate your customer reporting. Apply advanced formulas like IF, EOMONTH, and YEAR to instantly categorize sales transactions with zero hassle.
- 1. Open your dataset: Launch WPS Spreadsheet and open the file containing your customer transaction dates.
- 2. Enter your formula: Click on the status cell and type your IF and EOMONTH combination formula to compare the dates.
- 3. Use AutoFill: Drag the AutoFill handle down to apply the logic to all customers in your dataset seamlessly.
- 4. Filter your results: Go to the Data tab and click Filter to easily sort or view only your 'New' or 'Current' customers.

Frequently Asked Questions
Why does my formula return 'Current' even when the months are exactly the same?
This usually happens if the years are different, or if one of the cells is formatted as text rather than a valid date. Ensure you check your cell formats and compare both the year and the month.
How can I automatically color code the 'New' customers in my spreadsheet?
You can use Conditional Formatting. Select your status column, navigate to Home > Conditional Formatting > Highlight Cell Rules > Equal To, type 'New', and select your preferred highlight color.
Does the EOMONTH function work differently than the MONTH function?
Yes. The MONTH function simply extracts the month number (1 to 12), which requires you to also verify the YEAR separately. EOMONTH returns the exact last day of the month for a specific date, inherently accounting for both the month and the year in a single value.




