🧠 AI Learning Hub

Hub › Lessons › Lesson 6

Lesson 6 · Freelance money adminAbout 20 minutes

Build a Notion income tracker that flags late invoices, with AI help

A list of invoices only helps if it tells you what to do next: who has paid, who is late, and who needs a reminder this week. In this lesson you build a small Notion database that works that out from three dates. You get four formulas we tested in Notion, and a prompt for asking an AI assistant to write new formulas. You also see why you should never paste an AI-written formula without testing it first: the first one AI wrote for us looked right and was wrong.

What you will be able to do: keep your invoices in one Notion table that shows each one as Paid, Waiting, Overdue or missing a due date, counts the days overdue, and gives you a short "who to chase" list. You will also know how to get AI to write a new formula and check it with four test rows.

What you need

Step 1: Create the database and its six basic properties

  1. Open a Notion page, type /database and pick a database. Name it Income. New databases are created as tables.
  2. Rename the first column (the title) to Invoice.
  3. Click the + next to the last column to add each property below. Type the name exactly as shown, because the formulas refer to these names.

The six properties you type in yourself. On a phone, scroll the table sideways.

Property nameTypeWhat goes in it
InvoiceTitleInvoice number and a short label, e.g. "INV-015 Monthly retainer"
ClientSelectOne option per client
AmountNumberThe invoice total. You can format it as currency.
Invoice dateDateThe day you sent the invoice
Due dateDateThe day payment is due
Paid dateDateLeave empty until the money arrives

There is no "Status" property to update by hand. The status is worked out from the dates, so it cannot fall out of date when you forget to change it.

Step 2: Add four tested formulas

For each one: click + to add a property, choose Formula, give it the name shown, select Edit formula, paste the formula and save. Add them in this order, because the second formula uses the first.

Formula: Payment status
ifs(not empty(prop("Paid date")), "Paid", empty(prop("Due date")), "Add a due date", prop("Due date") < today(), "Overdue", "Waiting")

Paid if there is a paid date; a reminder to fill in the due date if it is missing; Overdue if the due date has passed; otherwise Waiting.

Formula: Days overdue
if(prop("Payment status") == "Overdue", dateBetween(today(), prop("Due date"), "days"), 0)

How many days past the due date an unpaid invoice is. Paid and not-yet-due invoices show 0.

Formula: Days to get paid
if(empty(prop("Paid date")), 0, dateBetween(prop("Paid date"), prop("Invoice date"), "days"))

How long each client took to pay. Unpaid invoices show 0, so filter to Paid before you compare clients.

Formula: Month
formatDate(prop("Invoice date"), "YYYY-MM")

The invoice month as text, such as 2026-10, so you can filter or group by month.

Check them with four example rows

Enter these rows (they are made up for practice), changing the dates so they sit around today's date in the same way. If your results match, the formulas are working.

Example rows and the results Notion gave us on 22 October 2026 (Singapore time). On a phone, scroll the table sideways.

InvoiceInvoice dateDue datePaid datePayment statusDays overdueDays to get paid
INV-0141 Sep15 Sep19 SepPaid018
INV-01530 Sep14 Oct(empty)Overdue7*0
INV-01615 Oct29 Oct(empty)Waiting00
INV-01720 Oct(empty)(empty)Add a due date00

INV-014 is a useful trap: its due date has passed, but it was paid, so it must not show as Overdue.

* The count depends on the date Notion treats as "today". Our check ran at about 3:50 am Singapore time through Notion's API, which counted today as 21 October and gave 7. Your Notion app may use your own time zone and show 8. A difference of one day around midnight is expected.

Step 3: Add a "To chase" view

  1. Select + next to your database's view name, choose Table and name it To chase.
  2. Open the settings menu at the top of the database, select Filter, choose Payment status and set it to Overdue.
  3. In the same menu, select Sort and sort by Days overdue, highest first.

Open this view once a week. Everything in it needs a reminder; Lesson 2 has a prompt for writing one that stays polite and firm.

Step 4: Get AI to write a new formula, then test it

Sooner or later you will want something the four formulas do not cover. An AI assistant can write it, as long as you give it your exact property names and ask for test rows. Copy this prompt, fill in the parts in square brackets, and paste it into your AI assistant. You only need to share property names and types, not client names or amounts.

Copyable prompt: write a Notion formula
I keep my invoices in a Notion database. These are its properties, with their types:
[list each property as: - Name (type)]

Write a Notion formula for a new formula property called "[name]". It should:
[describe exactly what it should show, and when]

Rules:
- Use Notion's current formula language and refer to properties as prop("Property name"), using the names above exactly.
- If a date is empty, the formula must not show an error.
- Give me the formula on its own first, ready to paste.
- Then explain each part in one short line.
- Then give me 4 test rows (the dates to enter, assuming today is [today's date], and the result I should see), including one paid invoice and one with no due date.
- If anything in my request is unclear, say what you assumed.

Then test it before you trust it: add the formula, enter the test rows (plus one row where the date is right on the edge, such as due tomorrow), and compare what Notion shows with what the AI predicted. If anything differs, send this:

Copyable prompt: report a wrong result
I tested your formula in Notion. It does not give the results you predicted:
- [row: the dates you entered] shows "[what Notion shows]". Expected: "[what it should show]".
- [repeat for each row that is wrong]
Find the cause, give me a corrected formula on its own first, and explain the fix in two lines.

Worked example: the formula AI got wrong

On 22 October 2026 we asked Claude, using the prompt above, for a Next action formula: show "Send a reminder" when an unpaid invoice is 7 or more days overdue, "Due soon" when it is due in the next 3 days, and nothing otherwise. The answer looked convincing. It came with a clear explanation and four test rows, and the explanation said the reminder check meant "due date is 7+ days in the past".

We added the formula in Notion and entered four unpaid invoices. It was wrong for every one of them:

Claude's first formula in Notion, compared with what we asked for. On a phone, scroll the table sideways.

Unpaid invoice dueNotion showedShould show
14 Oct (about a week ago)(nothing)Send a reminder
19 Oct (a few days ago)Due soon(nothing)
23 Oct (tomorrow)(nothing)Due soon
29 Oct (next week)Send a reminder(nothing)

The cause was the order of the two dates. In Notion, dateBetween(a, b, "days") counts the days from b to a: it is positive when a is later than b. The first formula had the dates the wrong way round, so past and future were swapped. The paid invoice and the one with no due date came out right, so a quick test with only those rows would have missed the problem.

We sent the "report a wrong result" prompt with the table above. Claude replied that "past dates give negative numbers and future dates give positive numbers" and flipped the comparisons. We tested the corrected formula on the same rows, and it gave the right result for all of them. Here is an equivalent version written with today(), which we find easier to read. It passed the same test:

Formula: Next action (tested)
ifs(not empty(prop("Paid date")) or empty(prop("Due date")), "", dateBetween(today(), prop("Due date"), "days") >= 7, "Send a reminder", dateBetween(prop("Due date"), today(), "days") >= 0 and dateBetween(prop("Due date"), today(), "days") <= 3, "Due soon", "")

The lesson: AI is good at Notion's formula language and quick to fix its own mistake once you show it one. It is not reliable about which way a date difference runs. Four test rows, including one on each side of today, take two minutes and catch this.

Watch out for

Next step

How we checked this lesson