How to Use Excel Formulas to Mark Dates as Delayed or On Schedule
Question details
The user needs an Excel formula to compare completed project kick-off dates with planned dates to categorize their status, and then apply a red highlight to the delayed items.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking project timelines where visual indicators and status texts are needed to quickly identify if tasks are falling behind their planned schedule.
- Observed behavior
- The goal is to automatically display 'Delayed', 'On schedule', or 'Early' based on Date comparisons and turn the delayed status cells red.
Ensure that both your planned date and completed date columns are formatted correctly as Dates (not as plain text) so the formulas can evaluate them accurately.
Use a Basic IF Formula with Conditional Formatting
Best for simply categorizing dates as either 'Delayed' or 'On schedule or early' and applying a red highlight to the delayed items.
This method uses a straightforward logical test to check if the completed date is greater (later) than the planned date. Conditional formatting is then layered on top to provide a visual warning.
Select the status cell (e.g., R4) and enter the formula: =IF(Q4>P4,"Delayed","On schedule or early"). Press Enter.
Click the bottom-right corner of cell R4 and drag the fill handle down to copy the formula for all the rows in your table.
Keep the status range selected. Go to the Home tab on the ribbon, click 'Conditional Formatting', and select 'New Rule'.
Choose 'Use a formula to determine which cells to format'. Enter the formula =Q4>P4. Click the 'Format' button, choose a red fill color, and click OK.
Use a Nested IF Formula for Three Statuses
Use this approach if you need to explicitly distinguish between 'On schedule' (an exact date match) and 'Early' (completed before the planned date).
Easily Track Project Dates with WPS Spreadsheet
WPS Office provides a powerful, highly compatible spreadsheet tool that fully supports Excel IF formulas and Conditional Formatting. You can seamlessly track project timelines and highlight delayed tasks without any hassle.
- 1. Open your project tracker: Launch WPS Spreadsheet and open your project timeline workbook.
- 2. Apply the IF formula: Enter =IF(Q4>P4,"Delayed","On schedule") into your target status cell and drag it down the column.
- 3. Highlight delayed tasks: Go to the Home tab, click Conditional Formatting, and use the 'Highlight Cells Rules' to quickly apply a red background to cells containing the word 'Delayed'.
Frequently Asked Questions
Why does my IF formula return an error or incorrect result when comparing dates?
This usually happens if one of your date columns is stored as text rather than a date value. Excel cannot properly compare text strings mathematically. Select your date columns, go to the Data tab, and use 'Text to Columns' or format them explicitly as a Short Date to fix this.
Can I highlight the entire row instead of just the status cell when a project is delayed?
Yes. Select your entire data range (e.g., A4:R20) before applying Conditional Formatting. Choose 'Use a formula to determine which cells to format', use a formula with locked columns like =$Q4>$P4, and apply your desired red fill. This ensures the entire row changes color based on the date comparison.
How do I calculate the exact number of days a project is delayed?
You can subtract the planned date from the completed date. Use a formula like =IF(Q4>P4, Q4-P4, 0) to find the exact number of days delayed. If the project is on time or early, it will display 0.
