Function Problems

How to Identify Past-Due Dates and Move Completed Rows in Excel

Algirdas Jasaitis Sep 27, 2026 869 views

Question details

The user needs a formula to flag overdue items based on their dates and a method to automatically move rows marked as completed to a different worksheet.

How to Identify Past-Due Dates and Move Completed Rows in Excel

Product: Excel
Device & OS: not provided
Scenario: Tracking task deadlines and organizing completed items across multiple worksheets.
Observed behavior: Needing a conditional formula to display status text and a VBA or automation solution to physically move data rows.

Before you start

Ensure your deadline column is properly formatted as Dates rather than Text, and save a backup copy of your workbook before running any VBA macros.

Solution 1Recommended

Use the IF and TODAY Functions to Identify Past-Due Dates

Apply a simple logical formula to dynamically flag items that have passed the current date.

Select the target status cell

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

Enter the IF formula

Type =IF(TODAY()>B2, "Past Due", "Current") into the formula bar, assuming your due date is located in cell B2.

Apply to the entire column

Press Enter to evaluate the formula, then click and drag the fill handle at the bottom-right corner of the cell downwards to apply it to the rest of your rows.

Use the IF and TODAY Functions to Identify Past-Due Dates

Customizing the Output: You can replace "Current" with an empty string ("") if you prefer the cell to remain blank when an item is not past due.

Solution 2

Automate Moving Completed Rows with VBA

Since formulas cannot move data across sheets, use a VBA macro to transfer rows with a 'Completed' status.

Manage Tasks Effectively in WPS Spreadsheet

Easily Track Overdue Dates and Run Macros in WPS Office

WPS Spreadsheet provides robust support for logical formulas like IF and TODAY, alongside full compatibility with VBA macros for automating task management workflows seamlessly.

1. Open your task tracker: Launch WPS Spreadsheet and open your existing task management document.
2. Input the status formula: Select the status column and type the formula =IF(TODAY()>B2, "Past Due", "").
3. Fill the formula down: Double-click or drag the fill handle to apply the formula to all corresponding rows.
4. Run automation macros: Navigate to the Tools tab to access the VBA Editor, where you can paste and run scripts to move completed rows to another sheet.

Fully compatible with Microsoft Excel .xlsx and .xlsm formats.Supports standard logical formulas like IF, TODAY, and advanced conditional formatting.Includes a built-in VBA editor (in applicable versions) for running automation scripts.Free, lightweight, and features a familiar tabbed interface.

microsoft office alternative - wps office

Frequently Asked Questions

Can Conditional Formatting highlight past-due dates instead of text?

Yes. Select your date column, click Conditional Formatting > Highlight Cells Rules > Less Than, and type =TODAY(). Choose a red fill format to automatically highlight past-due dates.

Why does my IF formula return an error or incorrect result?

This usually happens if the referenced cells are formatted as Text rather than Dates, or if there are invisible spaces in the cell. Highlight the column, right-click, select Format Cells, and ensure 'Date' is selected.

Is it possible to move completed rows without using VBA?

Formulas alone cannot move rows. If you prefer not to use VBA or Power Automate, you can manually apply a Filter to the status column to show only 'Completed' items, then cut and paste them to your new sheet.

Excel & Data Problems