Function Problems

Excel Formula to Compare Dates and Return Task Status

Anum MushtaqAnum Mushtaq Nov 18, 2026 868 views

Question details

Return a task status by comparing its scheduled date, actual trigger date, and today's date.

Compare Dates and Return Task Status in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A task list contains IDs and dates, and each row needs a status such as Triggered On Time, Triggered Late, Past Due, or To be Triggered.
Observed behavior
Each task row should display a status based on whether it has been triggered and how its dates compare.
Before you start

Identify the scheduled date and actual trigger date columns. Make sure their values are recognized as dates rather than text, and adjust the cell references in the formula to match your worksheet.

Solution 1Recommended

Use a Nested IF Formula to Return Task Status

Use this formula when each task's scheduled and actual trigger dates appear in the same row.

The example uses column B for the scheduled date and column C for the actual trigger date. TODAY() supplies the current date, so no separate today's-date column is needed. Task IDs identify rows but are not used in the calculation.

The formula treats a task triggered before its scheduled date as Triggered Early. An untriggered task scheduled for today remains To be Triggered until tomorrow.

1
Arrange the task dates

In your worksheet, put scheduled dates in column B and actual trigger dates in column C. Leave the actual date blank for tasks that have not been triggered.

2
Enter the status formula

Select D2 and enter =IF(B2="","",IF(C2="",IF(B2<TODAY(),"Past Due","To be Triggered"),IF(C2=B2,"Triggered On Time",IF(C2>B2,"Triggered Late","Triggered Early")))).

3
Fill down the formula

Select D2 and drag its fill handle down the status column. Change B2 and C2 if your scheduled and actual dates are in different columns.

Use a Nested IF Formula to Return Task Status
Check date and time values: The formula compares complete cell values. If a date includes a time, an actual trigger on the scheduled day may compare as later; remove the time component or adjust the formula if you want to compare calendar dates only.
Calculate task status in WPS Spreadsheet

Compare Task Dates in WPS Spreadsheet

Use the same nested IF formula in WPS Spreadsheet to classify tasks by scheduled and actual trigger dates, with TODAY() supplying the current date.

  1. 1. Open your task list: Open WPS Spreadsheet and use Open to select your Excel workbook.
  2. 2. Check the date columns: Place or identify scheduled dates in column B and actual trigger dates in column C; leave untriggered dates blank.
  3. 3. Add the formula: Select D2 and enter =IF(B2="","",IF(C2="",IF(B2<TODAY(),"Past Due","To be Triggered"),IF(C2=B2,"Triggered On Time",IF(C2>B2,"Triggered Late","Triggered Early")))).
  4. 4. Apply and save: Drag the fill handle from D2 down the task list, then use File > Save As to save the workbook in XLSX format.
Calculate task statuses from scheduled and trigger datesOpen and save Microsoft Excel XLSX filesUse familiar spreadsheet formulas and controlsWork with spreadsheets in the free WPS Office suite
microsoft office alternative - wps office

Frequently Asked Questions

What happens if a task is triggered before its scheduled date?

The formula returns Triggered Early. To use another label, replace "Triggered Early" in the final part of the formula.

Why is a task scheduled for today not marked Past Due?

The formula marks an untriggered task Past Due only when its scheduled date is earlier than TODAY(). A task scheduled for today remains To be Triggered.

Can I use a cell containing today's date instead of TODAY()?

Yes. Replace TODAY() with a reference such as $E$2, where E2 contains the date to compare. The dollar signs keep the reference fixed when you fill the formula down.

Does the formula match tasks by ID?

No. It compares dates on the same row. If the dates are in separate tables, first use a lookup formula to bring the matching dates onto each task's row.

Why does the status remain blank?

The formula returns a blank when the scheduled date cell is empty. Check column B, or update B2 in the formula to point to your scheduled date column.