Essential Formulas: Text, Dates, and Logic

1. Understanding the Anatomy of a Formula

Before writing your first formula, it's essential to understand its basic building blocks. A Notion formula is constructed from a combination of elements that work together inside the formula editor.

To get a solid grasp of these core components and the editor itself, let's start with a video that breaks it all down.

To summarize the key components you'll use:

• Properties: These are the columns in your database (e.g., Due Date, Status, Name). They act as the variables in your formula.
• Functions: These are pre-built actions that perform a specific task, like adding time to a date (dateAdd()) or joining text (concat()).
• Operators: These are symbols used for calculations and comparisons, such as + (add), > (greater than), and == (equal to).
• Constants & Literals: These are fixed values you type directly into the formula, such as the number 100 or a piece of text like "Complete". Text values must always be enclosed in double quotes.

2. Conditional Logic with if Statements

Conditional logic is the heart of any dynamic system. It allows your template to make decisions and display different outputs based on specific criteria. The primary tool for this in Notion is the if() function.

The structure is simple: if(condition, value if true, value if false).

Let's say you want to display a clear status message based on a Done checkbox property. You could write:

if(prop("Done") == true, "✅ Complete", "In Progress")

This formula checks if the "Done" property is checked. If it is, it outputs "✅ Complete"; otherwise, it outputs "In Progress".

The video you just watched provides a fantastic deep-dive into creating these conditional statements.

3. Working with Dates

Date calculations are fundamental to project management and content creation systems. Formulas allow you to calculate deadlines, count down days, and check if items are overdue.

Here are three essential date functions:

• dateAdd(date, amount, unit): Adds a certain amount of time to a date. Useful for setting review dates or deadlines.
  • Example: dateAdd(prop("Start Date"), 2, "weeks") calculates a date two weeks after the start date.
• dateBetween(date1, date2, unit): Calculates the time between two dates. Perfect for "Days Left" or "Overdue By" counters.
  • Example: dateBetween(prop("Due Date"), now(), "days") calculates the number of days between the due date and today. now() is a special function that returns the current date and time.
• formatDate(date, format string): Converts a date into a text string with custom formatting.
  • Example: formatDate(prop("Due Date"), "dddd, MMMM D, YYYY") would display a date as "Tuesday, June 18, 2024".

Let's see a practical example that cleverly combines date functions with the if() statement we just learned.

4. Text Manipulation

Being able to combine and format text is key to creating clean, descriptive information in your templates. The most common text manipulation task is joining different pieces of text together, which is called concatenation.

In Notion, you can concatenate text using the + operator.

Example: Creating a Unique Task ID

Imagine you have a Projects database where one project is named "Website Redesign" and a related Tasks database with a task numbered 101. You want to create a unique ID like Website Redesign-101.

You could use a formula in the Tasks database like this:

prop("Project Name") + "-" + format(prop("Task Number"))

• prop("Project Name") pulls in "Website Redesign".
• + "-" + adds a hyphen.
• format(prop("Task Number")) is crucial. The + operator only works on text, so we must use format() to convert the Task Number (a number property) into a text string before we can join it.

For a set of clear, practical examples of common formulas, including text manipulation, this article is a great reference.

✍️ Exercise: Supercharge Your Project Manager

Let's apply these new skills to the "My First Connected System" you've been building.

Part 1: Dynamic Status (Conditional Logic)

In your Projects database, you have a Progress rollup. Let's create a formula that displays a more descriptive status.

• Go to your Projects database.
• Add a Date property named Due Date and set a due date for one of your projects that is in the past.
• Add a Formula property named Project Status.
• Enter the following formula. This is a nested if statement, which checks conditions in order.

  if(prop("Progress") == 1, "✅ Done", if(prop("Due Date") < now(), "🔥 Overdue", "⏳ In Progress"))
  

  This formula first checks if progress is 100% (or 1). If so, it shows "Done". If not, it then checks if the due date is in the past. If so, it shows "Overdue". Otherwise, it shows "In Progress".

Part 2: Days Left Counter (Date Calculation)

Let's add a countdown to the deadline.

• In your Projects database, add another Formula property. Name it Days Left.
• Enter this formula:

  dateBetween(prop("Due Date"), now(), "days")
  

• You will see a positive number for future dates and a negative number for past dates.

Part 3: Formatted Days Left (Text & Conditional Logic)

A raw number isn't very user-friendly. Let's combine everything we've learned to make it better.

• Create one more Formula property named Deadline.
• Enter this more advanced formula:

  if(prop("Project Status") == "✅ Done", "Completed!", if(prop("Days Left") < 0, "Overdue by " + format(abs(prop("Days Left"))) + " days", format(prop("Days Left")) + " days remaining"))
  

  • This formula checks the Project Status you created. If "Done", it says "Completed!".
  • Otherwise, it checks if Days Left is negative. If so, it displays an "Overdue by..." message. Note the use of abs() to show a positive number of days.
  • If the project isn't overdue, it shows the number of days remaining.

Once complete, your project database will have intelligent, automated status indicators that make it far more valuable.

Lesson Summary and Next Steps

Fantastic work! You have now unlocked the true power of Notion databases by learning to write Formulas. You've moved beyond simple data entry into the realm of building automated, intelligent systems.

Key Takeaways:

• Formulas Provide Custom Logic: Unlike Rollups, Formulas let you define your own rules, calculations, and text outputs.
• if() is for Decision-Making: Conditional logic is the key to making your templates feel responsive and "smart."
• Combine Functions for Power: The most powerful formulas often combine date, text, and conditional functions to produce a single, highly informative output.
• Data Types Matter: Always be mindful of whether you're working with text, numbers, or dates, and use functions like format() to convert between them when needed.

You've now learned how to connect databases with relations, aggregate data with rollups, and perform custom calculations with formulas. But how do you present all this powerful information in a clean, focused way?

In our next lesson, you will learn how to create linked databases. This will allow you to display the same database in multiple places, but with different filters and views applied—the key to building centralized dashboards.