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
- A Notion account and a page where you can add a database.
- Any AI chat assistant, such as Claude or ChatGPT, for the formula-writing step. Notion's formula editor has its own Notion AI helper, but Notion's help page says that feature is available on Business and Enterprise plans, so this lesson does not depend on it.
- A few real invoices, or use the example rows below to practise first.
Step 1: Create the database and its six basic properties
- Open a Notion page, type
/databaseand pick a database. Name it Income. New databases are created as tables. - Rename the first column (the title) to Invoice.
- 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 name | Type | What goes in it |
|---|---|---|
| Invoice | Title | Invoice number and a short label, e.g. "INV-015 Monthly retainer" |
| Client | Select | One option per client |
| Amount | Number | The invoice total. You can format it as currency. |
| Invoice date | Date | The day you sent the invoice |
| Due date | Date | The day payment is due |
| Paid date | Date | Leave 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.
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.
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.
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.
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.
| Invoice | Invoice date | Due date | Paid date | Payment status | Days overdue | Days to get paid |
|---|---|---|---|---|---|---|
| INV-014 | 1 Sep | 15 Sep | 19 Sep | Paid | 0 | 18 |
| INV-015 | 30 Sep | 14 Oct | (empty) | Overdue | 7* | 0 |
| INV-016 | 15 Oct | 29 Oct | (empty) | Waiting | 0 | 0 |
| INV-017 | 20 Oct | (empty) | (empty) | Add a due date | 0 | 0 |
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
- Select + next to your database's view name, choose Table and name it To chase.
- Open the settings menu at the top of the database, select Filter, choose Payment status and set it to Overdue.
- 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.
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:
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 due | Notion showed | Should 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:
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
- Property names must match exactly.
prop("Due Date")andprop("Due date")are different. If you rename a property, open each formula and check that it still works. - Dates with a time. We tested with plain dates only. If you switch on "Include time" for a date, the day counts may not match what you expect, so keep these three as plain dates or re-test with your own rows.
- Part-payments. This setup treats an invoice as paid or not. If clients often pay in instalments, add an "Amount paid" number and ask AI for a formula that compares it with Amount, then test it the same way.
- Privacy. To get a formula written, the AI only needs property names and made-up test dates. Do not paste client details or a full export into a chat assistant unless you are comfortable with that service's data settings.
Next step
Want this ready-made?
If you would rather start from a finished setup than build your own, the Notion Finance Dashboard product page lists an income and expense tracker and invoice management alongside budget and cash-flow views. The page shows what is included, the requirements and the price, so you can compare it with what you built here.
See what's inside the Finance DashboardMore lessons
Next: a 10-minute weekly money check that uses your "To chase" view. Earlier this week: write a clear invoice and reminder and run a monthly cash review.
All lessonsHow we checked this lesson
- On 22 Oct 2026 (Singapore time) we created a test database in a real Notion workspace with the properties and five formulas on this page, entered six illustrative invoices, and read the formula results back through Notion's API by filtering on each result. Payment status, Days overdue, Days to get paid and Month gave the results in the table above; Next action showed "Send a reminder" only for the invoice due 14 Oct and "Due soon" only for the one due 23 Oct.
- We created the formulas through Notion's API rather than by typing them into the formula editor. They use the same formula language, but we did not click through the editor itself on this run.
- The worked example used Claude Code 2.1.306 in print mode, with the prompt above filled in and then the "report a wrong result" prompt as a follow-up. Quotes are as returned. Both of Claude's formulas were tested in the same Notion database.
- Menu steps, the formula functions used (
ifs,empty,dateBetween,today,formatDate) and the plan note for Notion AI in the formula editor were checked against Notion's help pages on 22 Oct 2026: Intro to formulas, Formula syntax & functions, Database properties and Views, filters & sorts. Notion changes its menus from time to time; the help pages are the authority. - AI output varies. Your assistant may get the formula right first time, or wrong in a different way. Test every formula before you rely on it.
- Last reviewed: 22 October 2026.