9 Notion formulas that will make your databases smarter

As a freelancer with ADHD, I've tried dozens of productivity apps that I hope will magically help me get my business and life on track. I'll usually play around with each app for a few days until I get overwhelmed trying to adapt my workflows to the app's systems and fall back on my tried-and-true system of juggling dozens of spreadsheets and cluttering my email inbox with notes.

Notion completely changed all that.

Over the past year, I've been using Notion to get my work and life in order, and in the process, I've found lots of ways to make my Notion databases more efficient. One of my favorite features is Notion formulas, so I've created this list of the most helpful formulas I use—I hope you can use them to make your Notion databases smarter, too.

What are Notion formulas?

Notion formulas are snippets of code that perform operations on properties within a Notion page. Formulas can help you visualize data, perform calculations, combine information from multiple properties, automate data, and display dynamic information about your database.

You can add formulas to your databases using Notion's formula property, which uses a specific formula syntax to apply mathematical functions, booleans, and logical operators to the data within properties. 

When you add a database property to your formula, Notion will tell you its property type. When you add a built-in boolean or a function to the editor, Notion will tell you what it will do in your formula and provide some usage examples that you can use as a guide.

My favorite resource for learning new Notion formulas is Thomas J. Frank's Notion Formulas Cheat Sheet. It provides step-by-step tutorials and in-depth explanations of Notion's formula components. Many of the formulas in this article were built with the help of Thomas J. Frank's cheat sheet.

9 Notion formula examples

1. Show percent completion with a Unicode progress bar

Notion's number property has a built-in setting that displays numbers as percentages with progress bars, but it can only be used to display a single number property as a percentage of 100. If you want to create a progress bar that displays the ratio of two number properties, you'll need to use a formula. For example, my reading list database displays progress bars based on how much of each book I've read.

When I initially built my reading database, I added a simple formula that displays the completion percentage of each book using Notion's default progress bars.

Even though the default progress bars worked well, I wanted to add a bit of personality to my reading list, so I adjusted the formula to display the progress bars using Unicode characters.

The new progress bars use Notion's substring function to display each book's completion percentage using ten black and white Unicode stars. Each star represents ten percent of the book's page total. The exact completion percentage, rounded to the nearest percentage point, is displayed beside each progress bar.

The Unicode progress bars use the following formula:

substring("★★★★★★★★★★", 0, (Current Page/Total Pages)*10)+substring("☆☆☆☆☆☆☆☆☆☆", 0, (1-(Current Page/Total Pages))*10)+" "+round((Current Page/Total Pages*100))+"%"

2. Generate invoice numbers

Generating invoices is one of the most tedious aspects of owning a business for me, second only to pitching my services. I've started tracking my projects and drafting my invoices in Notion before finalizing them and creating PDFs in Canva to make things easier.

I've created a formula in Notion that extracts each client's initials from their full name and combines the initials with a shortened form of the date that the invoice will be sent to create an invoice number.

The invoice number column uses the following formula:

"INV-"+substring(First Name, 0,1)+substring(Last Name, 0,1)+"-"+formatDate(Invoice Date, "MMM")+formatDate(Invoice Date, "YYYY")

3. Display an overview of missing data with emojis

One of my favorite Notion formulas uses 🔴 and 🟢 emojis to indicate whether a checkbox is checked. I use this formula to create a list of action items for my clients when I audit their SEO content.

Each aspect of the content audit has a separate line in the formula. The messages that the formula displays will change depending on whether each property's checkbox is checked or unchecked. For example, if the KW in URL? checkbox is checked, the formula will return "Keyword in URL" with a 🟢 emoji. If the KW in URL? checkbox is not checked, the formula will return "Keyword not in URL" with a 🔴 emoji.

The emoji overviews use the following formula:

if(KW in URL? == true, "Keyword in URL 🟢","Keyword not in URL 🔴")+"\n"+if(Meta Description KW? == true, "Keyword in meta description 🟢","Keyword not in meta description 🔴")+"\n"+if(Full Title in SERPs? == true, "Full title visible in SERPs 🟢","Full title not visible in SERPs 🔴")+"\n"+if(Internal links up-to-date? == true, "All internal links up-to-date 🟢","Not all internal links up-to-date 🔴")

You can edit the names of the properties in this formula and the statements that accompany each emoji to customize the formula so it references the data in your database.

• To add emojis to your formula on a Mac, use the shortcut fn + E to access the character viewer, then go to the Symbols menu within the Emoji menu, and double-click on the 🔴 or 🟢 emoji.

• To add emojis to your formula on a PC, use the shortcut Windows key + period to access the emoji keyboard, then go to the Symbols menu within the Emoji menu, and click on the 🔴 or 🟢 emoji.

4. Count the number of days between the current date and a goal date

When I start a new project for a client, I like to use a formula to calculate the number of days between the day that I'm assigned the project and the project's deadline. Calculating the durations of my projects helps me break each project down into smaller, manageable tasks that I can complete each day. I don't track these smaller tasks in Notion. Instead, I create to-do lists of the tasks in Google Keep.

I use the following formula to calculate the number of days between a project's start date and its deadline:

dateBetween(prop("Project Due Date"),prop("Project Start Date"),"days")+" days"

5. Assign a priority level to a task based on its due date

When I'm working on multiple projects at once, I tend to have trouble prioritizing my tasks. I often find myself starting the most enjoyable tasks first, even when my other tasks have more urgent deadlines. I've tried to organize my tasks on a calendar based on their deadlines, but seeing all the tasks together on a cluttered calendar was overwhelming. It didn't help me overcome the urge to put off difficult tasks in favor of enjoyable ones, and it made my task paralysis worse.

It didn't take me long to realize that I needed a dynamic system that could keep track of my deadlines and organize my daily tasks based on how soon the tasks were due.

In Notion, I created a task management database with a formula that uses the now(), dateBetween(), and style() functions to assign a colored priority level label to each task.

• Tasks that are due within 7 days of the current date are given a red High Priority label.

• Tasks that are due more than 7 days after the current date but less than 14 days after the current date are given a yellow Medium Priority label.

• Tasks that are due more than 14 days after the current date are given a green Low Priority label.

Unlike my laptop's calendar app, my Notion database automatically updates each task's priority label based on the current date and the day that the task is due.

I use the following formula to assign colored priority labels to my tasks:

if(dateBetween(prop("Due Date"), now(), "days") <= 7, style("High Priority", "b","red","red_background"), if(and(dateBetween(prop("Due Date"), now(), "days") > 7,dateBetween(prop("Due Date"), now(), "days") < 14), style("Medium Priority", "b","yellow","yellow_background"), if(dateBetween(prop("Due Date"), now(), "days") > 14, style("Low Priority", "b","green","green_background"),"")))

6. Daily affirmations

Even though I've been freelancing for years, I deal with imposter syndrome almost every day, especially when I'm reaching out to new prospective clients.

To give myself extra motivation when I need a confidence boost, I've created a planner in Notion that lists the most important task for each day of the week and displays an affirmation each day:

• Monday: "Start the week off strong! 💪"

• Tuesday: "Break down your goals into small steps 🧩"

• Wednesday: "You've made it halfway through the week! Keep pushing! 🗻"

• Thursday: "It's Friday Eve! Stay focused on your goals! 🔭"

• Friday: "You've made it to the end of the week! Reflect on what you've accomplished this week. 🪞"

• Saturday: "It's the weekend! Take time to relax. 🛏️"

• Sunday: "It's time to prep for next week. What are your goals for the coming week? 📝"

I use the following Notion formula to display affirmations that change depending on the day of the week:

if(formatDate(prop("Date"), "dddd") == "Monday", "Start the week off strong! 💪", if(formatDate(prop("Date"), "dddd") == "Tuesday", "Break down your goals into small steps 🧩",if(formatDate(prop("Date"), "dddd") == "Wednesday", "You've made it halfway through the week! Keep pushing! 🗻", if(formatDate(prop("Date"), "dddd") == "Thursday", "It's Friday Eve! Stay focused on your goals! 🔭",if(formatDate(prop("Date"), "dddd") == "Friday", "You've made it to the end of the week! Reflect on what you've accomplished this week. 🪞",if(formatDate(prop("Date"), "dddd") == "Saturday", "It's the weekend! Take time to relax. 🛏️",if(formatDate(prop("Date"), "dddd") == "Sunday","It's time to prep for next week. What are your goals for the coming week? 📝",""))))))) 

7. Extract a domain from an email address and turn it into a clickable URL

Finding business owners who can benefit from my services and reaching out to introduce myself to them can be difficult, especially if I don't take careful notes while prospecting. I use Notion to keep track of information about my prospects and their businesses.

One way I save time while prospecting is by using a Notion formula to automatically turn my potential clients' email addresses into their businesses' URLs so I can quickly access them to learn about what each business offers and how my services may be able to help them.

The URL formula in my prospecting database uses a regular expression (Regex) to extract the domain from the point of contact's email address and affix "https://www." to the beginning of the result to create a live hyperlink. For example, the formula would turn the email address jdoe@example.com into the URL https://www.example.com.

To extract clickable URLs from my prospects' email addresses, I use the following formula with regular expressions:

"https://www."+match(prop("Email"),"(?<=@)(.*\w)")

8. Color-code income and expenses

As a freelancer, my income fluctuates a lot from month to month as I wrap up projects with my clients and take on new clients. Color coding my transactions makes it easy for me to see how often I'm spending money and how often I'm making money, so I can get a clear picture of how much I need to make in the following month and how much I can afford to spend.

I track my spending and earnings in Notion using a Regex formula to bold my profits in green and bold my expenses in red within a set of parentheses. The Profit/Loss formula references an Amount number property, where I manually input the profit or loss amount of each transaction.

To color code my profits and losses, I use the following regular expressions formula:

ifs(test(prop("Amount"),"^\d+$"),style("$"+prop("Amount")+".00","b","green"), test(prop("Amount"),"^\d+\.\d{1}$"),style("$"+prop("Amount")+"0","b","green"), test(prop("Amount"),"^\d+\.\d+$"),style("$"+prop("Amount"),"b","green"), test(prop("Amount"),"^\-\d+$"),style(replace(prop("Amount"),"-","(-$")+".00)","b","red"), test(prop("Amount"),"^\-\d+\.\d{1}$"),style(replace(prop("Amount"),"-","(-$")+"0)","b","red"), test(prop("Amount"),"^\-\d+\.\d{2}$"),style(replace(prop("Amount"),"-","(-$")+")","b","red"),"")

The formula converts the value in the Amount property into a text string, so you won't be able to perform calculations on the formatted text. The formula also doesn't display thousands separators, so if you enter -$1,500 in the amount column, for example, the Profit/Loss column will display (-$1500) without a comma.

9. Subscription tracker

I use dozens of subscription-based apps in my freelance business and my personal life, and every month, I get surprised by emails from streaming services, tax software, my website provider, and business management tools thanking me for paying subscription fees that I'd completely forgotten about.

In an attempt to save my sanity and my wallet from forgotten subscription costs, I've added my subscriptions to a Notion database that tracks their upcoming renewal dates and costs.

When I add a new subscription to the database, I list the name of the service, the subscription cost, and the date of the last subscription payment. I also select whether the subscription renews monthly or annually.

I use the following formula in the subscription database to calculate the next renewal date of each subscription based on its renewal frequency:

if(prop("Frequency") == "Monthly", dateAdd(prop("Last Renewed On"), 1, "months"), if(prop("Frequency") == "Annually",dateAdd(prop("Last Renewed On"), 1, "years"),""))

If a subscription's renewal frequency is set to monthly, the formula displays the date that's one month after the last renewal date. If a subscription's renewal frequency is set to annually, the formula displays the date that's one year after the last renewal date.

Automate your Notion databases

Try adding these formulas to your databases and experimenting with your own formulas. Start by thinking about what you'd like your formula to do within your database and breaking the function down into small steps, then research the capabilities and syntax of Notion's operators, and input your database's properties into the formula as needed.

Then add even more power to your Notion databases with Zapier, so you can build automated systems that connect Notion to all the other apps you use. Zapier can do things like automatically add tasks to Notion, create Notion tasks from Slack with AI, and connect Outlook to Notion, among all sorts of other options.

Learn more about how to automate Notion, or get started with one of these pre-made workflows.

Related reading:

• What is Notion AI? And how to use it

• Notion tips and tricks for a more powerful workspace

• The best Notion alternatives