Formula Errors

Excel Formula to Count Days When Status is Closed and Highlight Open Rows

Maira Mehtab Sep 22, 2026 868 views

Question details

The user wants to calculate the number of days between two dates exclusively when a project or task status is marked as 'Closed', and apply conditional formatting to highlight entire rows when the status is 'Open'.

Product: Excel
Device & OS: not provided
Scenario: Tracking task or project durations based on specific status conditions in a spreadsheet.
Observed behavior: Needs a specific formula combination to calculate days conditionally and a formatting rule to highlight active rows.

Before you start

Ensure your spreadsheet has dedicated columns for the start date, end date, and status. Verify that your date columns are properly formatted as Dates rather than Text to prevent calculation errors.

Solution 1Recommended

Use the IF and DAYS Functions to Calculate Duration

Combine the IF function with the DAYS function to output the date difference only if the status cell contains the word 'closed'.

This formula checks the status column first. By nesting the LOWER function, it ensures the condition works regardless of how 'Closed' is capitalized.

Locate your reference cells

Identify the cells containing your start date (e.g., D5), end date (e.g., H5), and status (e.g., F5).

Enter the IF formula

In the cell where you want the counted days to appear, enter the formula: =IF(LOWER(F5)="closed",DAYS(H5,D5),"").

Apply to other rows

Press Enter to get the result. Drag the fill handle down from the bottom-right corner of the cell to apply this formula to the rest of your dataset.

Understanding the LOWER Function: The LOWER function converts all text in the target cell to lowercase during the check, ensuring that variations like 'Closed' or 'CLOSED' are still recognized accurately.

Solution 2

Apply Conditional Formatting for Open Statuses

Use a formula-based conditional formatting rule to highlight entire rows when a specific cell's status is 'Open'.

Smart Spreadsheet Tools

Easily Manage Formulas and Conditional Formatting in WPS Office

WPS Spreadsheet provides seamless support for advanced formulas like IF and DAYS, along with intuitive conditional formatting tools to help you track project statuses effortlessly.

1. Open your document: Launch WPS Spreadsheet and open your project tracker file.
2. Input the formula: Type the =IF(LOWER(F5)="closed",DAYS(H5,D5),"") formula in your target duration cell.
3. Apply conditional formatting: Navigate to Home > Conditional Formatting > New Rule to highlight your 'Open' rows.

100% compatible with Microsoft Excel formulas and formatting rules.Free and lightweight alternative for robust spreadsheet management.Intuitive interface for managing complex conditional formatting.Easily calculate date differences and track project timelines.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my DAYS formula returning a #VALUE! error?

This error usually occurs if your date cells are formatted as text instead of actual dates. Select your date columns, right-click, choose 'Format Cells', and set them to 'Date'.

How do I lock the column reference in my conditional formatting rule?

Use a dollar sign ($) before the column letter in your formula, such as `$F5`. This ensures that the formatting applies across the entire row based strictly on the value in column F.

Can I count days excluding weekends?

Yes, to exclude weekends, replace the DAYS function with NETWORKDAYS. Your new formula would be `=IF(LOWER(F5)="closed",NETWORKDAYS(D5,H5),"")`.

Does the formula work if 'Closed' is capitalized differently?

Yes, because the formula uses the LOWER function (`LOWER(F5)`), it converts any text in the cell to lowercase before evaluating, meaning 'CLOSED', 'Closed', and 'closed' will all trigger the correct calculation.

Excel & Data Problems