Solved: Notion formula to calculate business days between dates while excluding dates matching a related database of holidays

📌 The Problem Explained

Project managers and team leads often use Notion to track project timelines. However, Notion’s native dateBetween() function calculates the total number of calendar days, including weekends and public holidays.

To get an accurate “Level of Effort” (LOE), you need to calculate Business Days (Monday through Friday) and subtract specific dates found in a separate Holidays database. Without this, your project schedules will look shorter than they actually are, leading to missed deadlines and resource burnout.

💡 The Quick Solution

This formula uses Notion Formulas 2.0 logic. It generates a list of all dates between your start and end, filters out weekends, and then subtracts any dates linked via your Holidays relation.

The Formula:

/* 1. Define the Date Range and Holiday Dates */
let(allDates,
  repeat(0, dateBetween(dateEnd(prop("Date Range")), dateStart(prop("Date Range")), "days") + 1)
    .map(dateAdd(dateStart(prop("Date Range")), index, "days")),

  /* 2. Filter out Weekends (Saturday = 6, Sunday = 0) */
  let(workDays,
    allDates.filter(day(current) != 0 && day(current) != 6),

    /* 3. Filter out dates that exist in the Holidays Relation */
    workDays.filter(
      !prop("Holidays").map(current.Date).includes(current)
    ).length()
  )
)

🛠️ Step-by-Step Breakdown

To make this formula work, you must set up your databases with specific property types.

Step	Action	UI Path / Property Type	Purpose
1	Create a Holidays Database	New Database	To store all observed holidays as individual pages.
2	Add a Date property to Holidays	+ Add property > Date	Name this property Date.
3	Create a Relation	+ Add property > Relation	Link your “Tasks” database to your “Holidays” database.
4	Connect Holidays to Tasks	Relation Property	Select all relevant holidays for that specific task or project.
5	Add the Formula	+ Add property > Formula	Paste the code from the “Quick Solution” section.

How the Logic Works:

1. repeat() and map(): This creates an array of every single date between your start and end points.
2. day(current): This checks the day of the week for every date in that array. It excludes 0 (Sunday) and 6 (Saturday).
3. includes(current): This cross-references the remaining workdays against the dates you have linked in the Holidays relation property.
4. .length(): Instead of listing the dates, this counts the final number of filtered items, giving you the total business days.

⚠️ Common Mistakes to Avoid

1. Incorrect Property Names: Notion formulas are case-sensitive. If your date property is named Timeline, you must change prop("Date Range") in the formula to prop("Timeline").
2. Empty Relations: If you do not link any holiday pages to your task, the formula will still work and subtract weekends, but it won’t subtract any holidays. Ensure all applicable holidays are selected in the Holidays relation field.
3. Timezone Shifts: Ensure both databases are set to the same timezone settings. If a holiday starts at 11:00 PM in one timezone and 2:00 AM in another, the includes() function may fail to find a match.