Calculate Receivable Days Overdue with Copilot in Excel
What the source describes
Microsoft Support explains that Copilot in Excel can add columns that calculate new values from your existing data, such as durations, and explain how each formula works. A short YouTube tutorial applies this to receivables: Copilot suggests a days-overdue formula, the formula is then edited to count from today when the paid-in-full date is blank and to handle partial payments, and the new column is sorted from largest to smallest to focus on the longest overdue accounts.
Result or outcome
According to the sources, Copilot created a working days-late formula with an explanation, revised it to handle invoices without a paid-in-full date, and sorting the new column put the longest overdue accounts at the top.
How to run it
- Open the workbook in Excel for Microsoft 365 or Excel for the web, signed in with a Microsoft 365 plan that includes Copilot.
- Format the invoice list as an Excel table (Home > Format as Table) with one header row, unique headers, and no blank rows or merged cells: customer, invoice, due date, amount, amount paid, paid date.
- Select Home > Copilot (or the Copilot icon in the lower-right corner of Excel) and ask: 'Add a Days Overdue column to [table name] that counts days from Due Date to Paid Date, or to today if Paid Date is blank, and shows 0 if not yet due.'
- Read the formula and its explanation; if Copilot adds the column directly, check it, otherwise insert the suggested formula.
- Repeat for the Balance and Aging Bucket columns (one column per request), then sort Days Overdue from largest to smallest.
Try it yourself
Copyable prompt
Add a Days Overdue column to [table name] that counts days from Due Date to Paid Date, or to today if Paid Date is blank, and shows 0 if not yet due. The table has the columns [Customer], [Invoice], [Due Date], [Amount], [Amount Paid] and [Paid Date]. Explain the formula in one line. Next request: Add a Balance column to [table name] that is Amount minus Amount Paid. Next request: Add an Aging Bucket column to [table name] that groups Days Overdue into Current, 1-30, 31-60, 61-90 and 90+.
Replace the [bracketed] placeholders. Remove client names, account numbers, and personal data before pasting into any AI tool.
What to review
Test each inserted formula by hand on a few invoices: one paid, one partly paid, one not yet due and one with no paid date. Confirm the balance and aging totals tie to the accounts receivable balance in the accounting system. Check that the date Copilot uses for today is the report date you intend, since the results change each day.
Related free download: 50 AI Prompts for Bookkeepers (Prompt pack (PDF))
Source: Microsoft Support (Get data insights with Copilot in Excel); also Microsoft Support (Copilot in Excel tips); also YouTube (Copilot in Excel tutorial)
Not professional advice. This is general information, not accounting, tax, legal or financial advice. Check AI output yourself and consult a licensed professional for your situation. See our Terms of Use.
This entry summarizes a public source. Features, plans, and availability change, so confirm details with the vendor. Tool names are trademarks of their respective owners.
Related workflows
- Reporting and Analysis
Find Trends and Outliers in Ledger Exports with Copilot in Excel
Microsoft Support explains that Copilot in Excel lets users ask questions in their own words to surface summaries, trends and outliers, returning results as charts, PivotTables or summaries. It can also add formula columns and rows (for example profit margin or percent of total), suggest single-cell formulas, build lookups such as XLOOKUP, and explain existing formulas. Microsoft advises being specific about which columns to analyze and to review and verify anything Copilot creates.
Microsoft Copilot, Microsoft Excel
Microsoft Support - Reconciliations
Reconcile two Excel tables with the Microsoft Financial Reconciliation agent
Microsoft Learn explains that the Financial Reconciliation agent in the Finance Agent add-in for Excel reconciles two data sets formatted as tables on one or two worksheets. The agent suggests the tables and uses AI to propose reconciliation vectors (mapping and monetary keys) that you can keep, edit or remove before selecting Reconcile data. The resulting report groups transactions as matched, potentially matched or unmatched and includes a generative AI summary, with a Troubleshoot transactions feature for the unmatched items.
Microsoft Excel, Microsoft Finance Agent +1
Microsoft Learn (product documentation) - Accounts Receivable
Apply Customer Payments to Invoices with Remittance Advice AI
Invoiced's cash application page describes a CashMatch AI algorithm that applies customer payments to the right accounts, invoices and balances, plus remittance advice AI where you upload a document and the system matches payments to invoices while flagging inconsistencies. It also mentions custom matching rules, a virtual lockbox for scanning paper checks, short-pay and over-pay handling, multi-invoice application and a confidence score from 1 to 100 percent per match.
Invoiced, Invoiced CashMatch
Invoiced (vendor product page) - Reporting and Analysis
Trace Formula Errors in an Inherited Workbook with Claude for Excel
Anthropic's documentation describes Claude for Excel as an add-in for Pro, Max, Team and Enterprise plans that brings Claude into Excel. It can answer questions about an open workbook with cell-level citations, adjust assumptions while keeping formula relationships intact, identify errors and their root causes, and work across multi-tab workbooks. It can also sort, filter, edit pivot tables and add data validation dropdowns. The documentation warns that files from external sources can contain hidden instructions that try to manipulate the add-in, and says to start with a trusted copy before asking Claude to edit widely.
Claude for Excel, Microsoft Excel
Claude Docs (Claude for Excel)
If you want a workbook for the books behind this collection workflow, the Small Business Bookkeeping Spreadsheet Bundle is on our sister store.