Task recipe · Beginner · 15 min

Receivables ageing report in Excel

এক্সেলে দেনাদারের এজিং রিপোর্ট

Get AI to write the Excel formulas for days overdue, ageing buckets and bucket totals, then test them on invoices where you already know the right answer.

Works well with Microsoft Copilot in ExcelChatGPTClaude

The problem

Management wants to see how much customers owe and for how long: not yet due, 1 to 30 days, 31 to 60, 61 to 90 and more than 90 days overdue.

What you need

  • A list of open invoices: customer code, invoice date, due date, amount outstanding
  • The report date
  • Your Excel version

Steps

  1. Lay out the invoice list with one row per invoice and the report date in a separate cell.
  2. Paste the prompt, changing the column letters to match your sheet.
  3. Enter the formulas and fill them down.
  4. Test the boundary days and reconcile the totals.

The prompt

Copy it, then replace the example figures and [brackets] with your own.

Prompt
Excel 365. Sheet "Open invoices":
A = customer code, B = invoice date, C = due date, D = amount outstanding (Tk).
Data is in rows 2 to 500. The report date is in cell H1.

Write formulas for:
1. E2: days overdue at the report date (0 if not yet due).
2. F2: ageing bucket: "Not due", "1-30", "31-60", "61-90" or "90+".
3. A summary in J2:K6 with the total outstanding for each bucket.

Explain each formula in one or two lines and tell me how to test it.

What the AI should give you

Sample AI reply
1. Days overdue (E2, fill down)
   =MAX(0, $H$1 - C2)
   Report date minus due date; MAX(0, ...) turns "not yet due" into 0.

2. Ageing bucket (F2, fill down)
   =IFS(E2=0,"Not due", E2<=30,"1-30", E2<=60,"31-60", E2<=90,"61-90", TRUE,"90+")
   IFS checks each condition in order and returns the first match.

3. Bucket totals (type the five bucket names in J2:J6, then K2 and fill down)
   =SUMIFS($D$2:$D$500, $F$2:$F$500, J2)

Test: give one invoice a due date exactly 30 days before H1 and another
31 days before. They should land in "1-30" and "31-60". The total of
K2:K6 must equal =SUM(D2:D500).

How to check it

Common mistakes

Learn more

More recipes

All recipes

Sample figures are for learning only. Check every AI answer before you rely on it.