Function Problems

How to Label Customers as New or Current Based on Date in Excel

Aamir Naveed AkramAamir Naveed Akram Nov 11, 2026 868 views

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.

How to Label Customers as New or Current Based on Transaction Date
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.
Before you start

Ensure your transaction dates and first-order dates are formatted as actual Date values, rather than text, to prevent formula calculation errors.

Solution 1Recommended

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.

1
Select the target cell

Click on the cell in your status column where you want the first customer label to appear (for example, C2).

2
Enter the IF formula

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")

3
Apply to the entire column

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.

Date verification: This method is highly robust as long as the referenced cells contain valid dates.
Smart Data Processing

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. 1. Open your dataset: Launch WPS Spreadsheet and open the file containing your customer transaction dates.
  2. 2. Enter your formula: Click on the status cell and type your IF and EOMONTH combination formula to compare the dates.
  3. 3. Use AutoFill: Drag the AutoFill handle down to apply the logic to all customers in your dataset seamlessly.
  4. 4. Filter your results: Go to the Data tab and click Filter to easily sort or view only your 'New' or 'Current' customers.
100% compatible with Microsoft Excel formulas and date formatsIntuitive formula builder with automatic syntax highlightingFree and lightweight spreadsheet solution for advanced data analysis
QA img-9

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.