Solved: How to use Notion Formula 2.0 to calculate workdays between two dates while excluding entries from a specific holiday relation property

📌 The Problem Explained

In professional project management, a simple “days between” calculation is rarely sufficient. Most businesses need to calculate Net Working Days to determine actual level of effort.

Standard Notion formulas often fail here because they don’t natively account for weekends or varying holiday schedules. A user might have a Projects database and a Holidays database. The challenge is creating a formula that:

1. Calculates the range between a Start Date and End Date.
2. Filters out Saturdays and Sundays.
3. Dynamically cross-references a Relation Property (Holidays) and subtracts any dates that fall within the project window.

💡 The Quick Solution

Use this formula in a new Formula property. Ensure your holiday relation is named Holidays and the date property within the Holiday database is named Date.

let(
  allDates,
  sequence(0, dateBetween(prop("End Date"), prop("Start Date"), "days")).map(prop("Start Date").dateAdd(current, "days")),
  allDates.filter(
    current.day() != 0 &&
    current.day() != 6 &&
    !prop("Holidays").map(current.prop("Date").formatDate("YYYY-MM-DD")).includes(current.formatDate("YYYY-MM-DD"))
  ).length()
)

🛠️ Step-by-Step Breakdown

To implement this, follow these steps to configure your database and the Formula 2.0 syntax.

Step	Action	Description
1	Create Relation	In your main database, click Add Property > Relation and select your “Holidays” database.
2	Link Holidays	Ensure the projects are linked to the specific holiday entries (e.g., “New Year’s Day”) via the relation.
3	Open Formula	Create a new Formula property and click into the Formula field to open the editor.
4	Define Variables	Use the let() function to create an array of all dates between the start and end point.
5	Filter Weekends	Use current.day() to exclude 0 (Sunday) and 6 (Saturday).
6	Filter Relation	Use map() on the holiday relation to extract dates, then compare them to the project dates.
7	Count Results	Append .length() to the end of the filter to return the total number of valid days.

Technical Logic Breakdown:

• sequence(): This creates a list of numbers from 0 to the total number of days in the project.
• map(): This converts those numbers into actual date objects by adding them to the Start Date.
• formatDate(): Essential for holiday comparison. It ensures that the time of day doesn’t cause the includes() check to fail (comparing “2023-12-25” to “2023-12-25” rather than differing timestamps).

⚠️ Common Mistakes to Avoid

1. Empty Holiday Relations: If a project has no holidays linked in the Relation Property, the formula works fine, but ensure you haven’t renamed the “Date” property inside your Holiday database, or the current.prop("Date") part of the formula will break.
2. Inclusive vs. Exclusive: The dateBetween function can be exclusive of the last day. If you want to include both the start and end date in your count, ensure your sequence covers the full span correctly.
3. Property Name Mismatch: Notion Formula 2.0 is case-sensitive. Ensure prop(“Start Date”) and prop(“End Date”) exactly match your column headers.