WPS Spreadsheet: Classify Excel Dates as Due, Overdue, or Okay
Question details
Create a formula that assigns a status to a date based on whether its month is current, earlier, or later than today.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A date is stored in cell A2 and needs to be compared with the current date for deadline or schedule tracking.
- Observed behavior
- The desired result is Due for dates in the current month, Overdue for dates in earlier months, and Okay for dates in future months.
Confirm that the values being evaluated are recognized as dates rather than text, and identify the source date cell, such as A2. Make sure the workbook can recalculate formulas using the current date.
Use SWITCH and EOMONTH to classify dates by month
Use this formula when the status should be based on the calendar month instead of the exact day.
EOMONTH converts both the current date and the source date to their respective month-end dates. SIGN then returns 0 for the current month, 1 for an earlier month, and a negative value for a future month.
Replace A2 with the cell containing your date, then fill the formula down for the rest of the column.
Click the cell where you want the result to appear, such as B2.
In the formula bar or directly in the cell, enter =SWITCH(SIGN(EOMONTH(TODAY(),0)-EOMONTH(A2,0)),0,"Due",1,"Overdue","Okay") and press Enter.
Select the completed formula cell and drag its fill handle down, or use Home > Fill > Down, to classify the remaining dates.
Use nested IF functions for broader formula compatibility
Use this alternative when SWITCH is unavailable or when you prefer a more familiar conditional formula.
Track Due, Overdue, and Future Dates in WPS Spreadsheet
WPS Spreadsheet lets you build a month-based date-status formula with TODAY, EOMONTH, SWITCH, or IF, then apply it across an entire worksheet. Its familiar spreadsheet interface makes it easy to monitor deadlines while maintaining compatibility with Microsoft Excel workbooks.
- 1. Open a spreadsheet: Launch WPS Office, choose Spreadsheet, and open an existing workbook or create a blank sheet.
- 2. Enter your dates: Place the dates you want to evaluate in a column, such as A2:A100.
- 3. Add the status formula: Select an adjacent cell and enter =SWITCH(SIGN(EOMONTH(TODAY(),0)-EOMONTH(A2,0)),0,"Due",1,"Overdue","Okay").
- 4. Apply the formula to all dates: Use the fill handle or Home > Fill > Down to copy the formula through the status column.
Frequently Asked Questions
What does the WPS Spreadsheet date formula return for a date later this month?
It returns Due because the formula compares calendar months, not just whether the date is before or after today.
How do I classify dates from the previous month as Overdue?
Use the EOMONTH and SWITCH formula with the source date in A2: =SWITCH(SIGN(EOMONTH(TODAY(),0)-EOMONTH(A2,0)),0,"Due",1,"Overdue","Okay"). Any earlier month produces Overdue.
Can I use this formula with dates in another column?
Yes. Replace A2 with the relevant source cell, such as D2, then copy the formula down. Relative references will update for each row.
Why does the formula show Okay for a future date?
Future-month dates make EOMONTH(TODAY(),0)-EOMONTH(A2,0) negative, so they do not match the 0 or 1 SWITCH cases and use the default result Okay.
