How to Count Overdue Tasks in Excel with Formulas and Pivot Tables

To calculate the number of overdue cells in a column, we need to compare the values in the column with a reference date, such as today’s date, and count how many of them are less than the reference date. This means that the values in the column should be formatted as dates, otherwise Excel will not be able to compare them correctly.

One way to do this is to use a formula that combines the SUM, IF, and TODAY functions. The SUM function adds up the values in a range that meet a certain condition. The IF function returns a value based on a logical test. The TODAY function returns the current date.

The formula would look something like this:

=SUM(IF(range<TODAY(),1,0))

This formula works as follows:

• The IF function checks each cell in the range and compares it with the TODAY function, which returns the current date.
• If the cell value is less than the current date, the IF function returns 1, otherwise it returns 0.
• The SUM function adds up all the 1s and 0s returned by the IF function, which gives the number of overdue cells in the range.

Procedures

To use this formula, follow these steps:

• Select a cell where you want to display the result, such as E2.
• Enter the formula =SUM(IF(range<TODAY(),1,0)), replacing range with the actual range of cells that contain the dates, such as B2:B10.
• Press Ctrl+Shift+Enter to enter the formula as an array formula. This is necessary because the IF function returns an array of values, not a single value.
• The formula will return the number of overdue cells in the range, such as 3.

Explanation

To understand how this formula works in detail, let’s look at an example with some sample data. Suppose we have a table like this:

We want to calculate how many tasks are overdue as of today, which is 1/26/2024. We can use the formula =SUM(IF(B2:B9<TODAY(),1,0)) in cell E2 to get the result.

Here is how the formula works step by step:

• The TODAY function returns the current date, which is 1/26/2024.
• The IF function compares each cell in the range B2:B9 with the current date, and returns an array of values like this:

{1;1;1;0;0;0;0;0}

• The 1s indicate that the cells are less than the current date, meaning they are overdue. The 0s indicate that the cells are equal to or greater than the current date, meaning they are not overdue.
• The SUM function adds up all the values in the array, and returns the result, which is 3.

Therefore, the formula returns 3, which is the number of overdue tasks in the table.

Scenario

To illustrate this formula with a real-world scenario, let’s imagine that we are managing a project that involves several tasks with different due dates. We want to keep track of how many tasks are overdue at any given time, and display the result in a dashboard.

We can use the formula =SUM(IF(range<TODAY(),1,0)) to calculate the number of overdue tasks, and use a conditional formatting rule to highlight the overdue cells in red.

Here is an example of how the dashboard might look like:

To create this dashboard, follow these steps:

• Enter the task names and due dates in columns A and B, starting from row 2.
• In cell C2, enter the formula =IF(B2<TODAY(),"Overdue",IF(B2=TODAY(),"Due today",IF(B2-TODAY()<=7,"Due soon","On track"))). This formula checks the due date and returns a status based on how close it is to the current date.
• Copy the formula down to cell C9 to fill the column.
• In cell E2, enter the formula =SUM(IF(B2:B9<TODAY(),1,0)). This formula calculates the number of overdue tasks using the same logic as before.
• Select the range B2:C9 and go to the Home tab. Click on Conditional Formatting and select New Rule.
• Select the rule type “Use a formula to determine which cells to format”.
• In the formula box, enter =B2<TODAY(). This formula will apply the formatting to the cells that are less than the current date, meaning they are overdue.
• Click on Format and select the Fill tab. Choose a red color and click OK.
• Click OK again to apply the rule. The overdue cells should be highlighted in red.

Other approaches

There are other ways to calculate the number of overdue cells in a column, such as using the COUNTIF function or a pivot table.

Using COUNTIF

The COUNTIF function counts the number of cells in a range that meet a certain criterion. We can use this function to count the number of cells that are less than the current date, which means they are overdue.

The formula would look something like this:

=COUNTIF(range,"<"&TODAY())

This formula works as follows:

• The COUNTIF function takes two arguments: the range of cells to count, and the criterion to apply.
• The criterion is a text string that specifies the condition to check. In this case, we want to check if the cell value is less than the current date.
• To concatenate the less than sign (<) with the current date, we use the ampersand (&) operator. This creates a text string like “<1/26/2024”.
• The COUNTIF function counts how many cells in the range meet this criterion, and returns the result.

For example, if we use the same table as before, we can use the formula =COUNTIF(B2:B9,"<"&TODAY()) in cell E2 to get the same result as before, which is 3.

Using a pivot table

A pivot table is a powerful tool that allows us to summarize and analyze data in various ways. We can use a pivot table to group the dates by their status, and count how many tasks fall into each group.

To create a pivot table, follow these steps:

• Select the entire table, including the headers, in columns A to C.
• Go to the Insert tab and click on PivotTable.
• In the Create PivotTable dialog box, choose a location for the pivot table, such as a new worksheet, and click OK.
• In the PivotTable Fields pane, drag the Due Date field to the Rows area, and the Task field to the Values area.
• Right-click on any cell in the Due Date column and select Group.
• In the Grouping dialog box, select Days as the grouping option, and enter 1 as the number of days. This will group the dates by day, instead of by month or year.
• Click OK to apply the grouping.
• In the PivotTable Fields pane, drag the Status field to the Filters area.
• In the pivot table, click on the drop-down arrow next to the Status field and select Overdue. This will filter the pivot table to show only the overdue tasks.
• In the Values area, right-click on any cell and select Value Field Settings.
• In the Value Field Settings dialog box, change the name of the field to Overdue Tasks, and change the summary function to Count. This will show the number of overdue tasks, instead of the sum of the task names.
• Click OK to apply the settings.

The pivot table should look something like this:

The grand total shows the number of overdue tasks, which is 3. We can also see the individual dates and the number of tasks for each date.