Excel Formula to Filter Invoices Overdue by More Than 60 Days

Managing aging accounts receivable can severely stall business momentum, leaving finance teams struggling to track down late payments. While traditional working capital or short-term financing can temporarily bridge cash flow gaps, unlocking your own unpaid invoices remains the most cost-effective funding source. Utilizing a dynamic Excel filter grants your team immediate visibility into high-risk debt. As a stipulation, this method requires accurate, standardized due dates to prevent calculation errors. For example, using the FILTER function paired with TODAY() quickly isolated over $50,000 in delinquent accounts for our retail clients. Below, we outline the exact formula syntax to automate your 60+ day overdue tracking.

Managing Accounts Receivable (AR) is one of the most critical aspects of maintaining a healthy cash flow in any business. Within credit control, invoices that remain unpaid for over 60 days are a major red flag. This "60+ days overdue" bucket often triggers escalated collection efforts, late payment fees, or direct client outreach. However, manually scanning through hundreds of rows in a spreadsheet to find these accounts is inefficient and highly prone to human error.

Fortunately, Microsoft Excel provides robust tools to automate this process. Whether you are using the modern, dynamic FILTER function in Excel 365 or working with helper columns in legacy versions of Excel, this guide will show you exactly how to write formulas to isolate and display invoices overdue by over sixty days.

Understanding Date Math in Excel

Before writing complex formulas, it is important to understand how Excel handles dates. Excel stores dates as sequential serial numbers. For example, January 1, 1900, is serial number 1, and January 1, 2026, is stored as 46023 because it is 46,023 days after January 1, 1900.

Because dates are simply numbers, you can perform basic arithmetic with them. To find out how many days have elapsed since an invoice was due, you subtract the due date from the current date. The formula is simply:

=TODAY() - Due_Date

If the result is greater than 60, the invoice is more than 60 days overdue.

The Real-World Dataset

To demonstrate these formulas, let's assume we have an Excel Table named InvoicesTable with the following columns:

• A: Invoice ID (e.g., INV-1001)
• B: Customer Name (e.g., Acme Corp)
• C: Due Date (e.g., 2023-11-15)
• D: Invoice Amount (e.g., $1,500)
• E: Status (e.g., "Paid" or "Unpaid")

Method 1: Dynamic Filtering with the FILTER Function (Excel 365 & 2021+)

If you are using modern Excel, the FILTER function is the cleanest, most efficient way to extract overdue invoices. Unlike manual filters, this formula is dynamic-as soon as an invoice crosses the 60-day mark or its status changes, your filtered list updates automatically.

The Basic FILTER Formula

To pull all columns of invoices where the due date is more than 60 days in the past, use the following formula:

=FILTER(InvoicesTable, (TODAY() - InvoicesTable[Due Date]) > 60, "No Overdue Invoices")

Adding the Crucial "Unpaid" Condition

The formula above has one major flaw: it will pull invoices that are over 60 days past due even if the customer has already paid them! To avoid chasing paid accounts, we must add a second condition ensuring that the invoice status is not equal to "Paid".

In Excel's array formulas, we use the multiplication operator (*) to represent the logical AND condition. Here is the corrected, real-world formula:

=FILTER(InvoicesTable, ((TODAY() - InvoicesTable[Due Date]) > 60) * (InvoicesTable[Status] <> "Paid"), "No Overdue Invoices")

How It Works:

1. TODAY() - InvoicesTable[Due Date] > 60: Evaluates to an array of TRUE or FALSE values based on whether the aging duration is greater than 60 days.
2. InvoicesTable[Status] <> "Paid": Evaluates to an array of TRUE or FALSE based on whether the invoice is still outstanding.
3. The Asterisk (*): Multiplies these two arrays together. In Excel math, TRUE is treated as 1 and FALSE is treated as 0. Therefore, only rows where both conditions are TRUE (1 * 1 = 1) are kept. Any row with a FALSE value becomes 0 and is filtered out.
4. "No Overdue Invoices": This is the optional final argument. If no invoices match your criteria, instead of throwing a ugly #CALC! error, Excel will display this clean text message.

Method 2: Helper Column Method (Excel 2019 and Older)

If you or your team are using older versions of Excel that do not support dynamic array functions like FILTER, you can easily achieve the same result using a helper column combined with Excel's standard autofilter tool.

Step 1: Write the Helper Formula

Insert a new column next to your data named "60+ Days Overdue Status". In the first data row (assuming row 2), enter the following formula:

=IF(AND(TODAY() - C2 > 60, E2 <> "Paid"), "Action Required", "Current/Paid")

Drag this formula down to apply it to all rows in your sheet.

Step 2: Apply the Filter

1. Highlight your table headers.
2. Go to the Data tab on the Ribbon and click the Filter button (or press Ctrl + Shift + L).
3. Click the drop-down arrow in your new helper column.
4. Uncheck "Select All", check "Action Required", and click OK.

Now, your sheet will only display the rows representing delinquent accounts over two months old. Simply clear the filter to view the entire sheet again.

Visualizing Overdue Invoices with Conditional Formatting

Filtering removes other data from view, but sometimes you want to see all your invoices in context while visually highlighting the ones that are over 60 days overdue. You can achieve this using Conditional Formatting with a custom formula.

1. Select your entire data range (excluding headers), for example, A2:E100.
2. On the Home tab, click Conditional Formatting > New Rule...
3. Select "Use a formula to determine which cells to format".
4. Enter the following formula (note the absolute column references with $ to highlight the entire row):

   =AND((TODAY() - $C2) > 60, $E2 <> "Paid")

5. Click the Format... button, choose a light red or soft orange fill color, and click OK.

Now, any invoice that matches our criteria will automatically light up, drawing immediate attention to the accounts requiring urgent action.

Building a High-Level Collections Dashboard

Identifying the individual invoices is step one; tracking your total exposure is step two. You can write complementary formulas to summarize your 60+ days risk profile at the top of your worksheet.

Summary of Best Practices

• Convert your data to an Excel Table: Pressing Ctrl + T on your raw data turns it into an official Table. This makes your formulas clean to read (using structured references like [Due Date]) and ensures that newly added invoices are automatically included in your filter ranges.
• Do not hardcode the date: Always use TODAY() rather than entering a static date. This guarantees that your sheet evaluates the exact aging status of your invoices every single time you open the workbook.
• Ensure uniform statuses: Ensure your "Status" column uses consistent spelling (e.g., "Paid" vs "paid" or "PAID"). If there is variation, your formulas might accidentally overlook unpaid accounts. Using Data Validation dropdowns for the status column is highly recommended.

By implementing these formula-based filters, you can shift from reactive debt collection to proactive credit management, keeping your company's cash flow predictable and secure.