How to Exclude Weekends in Tableau

Working with dates in Tableau can feel straightforward until you need to account for how a business actually operates - which is typically not on Saturdays and Sundays. Calculating the duration between two dates is simple, but when those dates cross a weekend, metrics like "days to resolve" or "shipping time" get inflated and misleading. This article will show you several practical methods to accurately exclude weekends in your Tableau calculations, giving you a truer picture of your business performance.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Exclude Weekends from Your Analysis?

Most business analytics revolve around business days. When your default date calculations include weekend days, you introduce noise and inaccuracy into your reports. This can misrepresent your team's efficiency and lead to skewed insights.

Consider these common scenarios:

• Support Ticket Resolution Time: A customer submits a ticket at 4 PM on a Friday. Your team resolves it at 10 AM on Monday. A simple DATEDIFF calculation shows a resolution time of 3 days. In reality, your team addressed it in less than one business day. Including the weekend makes your support team look 3x slower than they actually were.
• Project Management Timelines: A project milestone is completed 5 days after it starts. If those 5 days were Monday through Friday, it's an accurate reflection of work. But if it started on a Thursday and finished the following Tuesday, it also shows as 5 days elapsed, even though only 3 working days passed.
• Shipping and Logistics: A package is shipped on a Friday and delivered on a Monday. Standard calculations view this as a 3-day transit time. Anyone in logistics knows that for most carriers, it was simply the next business day's delivery.

By removing weekends, your metrics become aligned with operational reality. Your data will accurately reflect work hours, active business processes, and team capacity, leading to more reliable dashboards and better-informed decisions.

Method 1: The Classic DATEDIFF Calculation

The most common method for calculating business days between two dates involves a calculated field using Tableau's built-in date functions. The logic seems complicated at first, but it's based on a simple idea: take the total number of days and subtract two days for every full week that passes between the dates.

This approach is fast, efficient, and doesn't require any changes to your data source. Let's build it step by step.

Step 1: Create a Calculated Field

First, open the workbook connected to the data source containing your start and end dates. Right-click on a blank space in the data pane on the left and select "Create Calculated Field."

Step 2: Name Your Calculation

Give your new field a descriptive name, like "Business Days Elapsed." In this example, we'll assume you have a [Start Date] and an [End Date] field in your data.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 3: Enter the Formula

The core logic hinges on using DATEDIFF with different date parts (day, week) to isolate the weekend days. A widely-used and reliable formula for this is:

(DATEDIFF('day', [Start Date], [End Date]) + 1)
- 
(DATEDIFF('week', [Start Date], [End Date], 'saturday'))
-
(DATEDIFF('week', [Start Date], [End Date], 'sunday'))

How This Formula Works

Let's break down each part to understand what's happening:

• (DATEDIFF('day', [Start Date], [End Date]) + 1): This calculates the total number of days between the start and end dates. We add 1 to make the calculation inclusive of both the start and end dates.
• DATEDIFF('week', [Start Date], [End Date], 'saturday'): This counts the number of Saturdays that fall between your two dates. The optional start_of_week parameter is set to 'saturday', so it effectively counts how many times a Saturday occurs.
• DATEDIFF('week', [Start Date], [End Date], 'sunday'): Similarly, this counts the number of Sundays between the same date range.

By subtracting the count of Saturdays and Sundays from the total inclusive days, you are left with only the number of weekdays.

Example in action:

• Start Date: Friday, October 6, 2023
• End Date: Monday, October 9, 2023

The formula would process this as:

1. Total days: (October 9 - October 6) + 1 = 4 days (Fri, Sat, Sun, Mon)
2. Number of Saturdays: 1
3. Number of Sundays: 1
4. Business Days: 4 - 1 - 1 = 2 days

This accurately reflects the two business days (Friday and Monday) that passed.

Method 2: Handling Holidays for Advanced Accuracy

Excluding weekends is a great start, but what about public holidays? A project plan that includes Christmas Day or Thanksgiving will still be inaccurate. To solve this, you need to provide Tableau with a list of holidays to exclude.

This method builds on our first calculation by subtracting any holidays that fall within the date range.

Step 1: Create a Holiday List

First, you need a data source that lists your company's holidays. The easiest way is to create a simple spreadsheet (Excel or Google Sheets) with a single column. Give the column a clear name like [Holiday Date] and list all the relevant holidays.

Step 2: Add the Holiday Data to Tableau

In your Tableau workbook, add a new data connection and connect to the holiday spreadsheet you just created. You will now see both your original data source and the holidays list in the Data pane.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 3: Relate or Join the Data

Now, you need to tell Tableau how these two sources relate. Go to the "Data Source" tab. Drag your orders or events table onto the canvas first, then drag your holiday list. Tableau will prompt you to create a relationship.

Creating a straightforward join here won't work well. Instead, we want to count holidays that fall between a start and end date. This is a perfect use case for a calculated field within the relationship clause. Set up the relationship conditions like this:

• [Order Date] <= [Holiday Date]
• [Ship Date] >= [Holiday Date]

Note: depending on your specific version of Tableau and data setup, this can sometimes be more easily achieved with a Level of Detail (LoD) calculation to pre-calculate holidays per order.

Step 4: Create a Calculation to Count Holidays

With the relationship established, we can now create a calculation to count the number of holidays for each record (e.g., each order or project). Create a new Calculated Field and name it "Holidays Between Dates." The formula is simply:

COUNTD([Holiday Date])

The COUNTD (Count Distinct) will count the unique holidays related to each record based on the relationship we just set up. It’s crucial here to remember one thing: make sure your Holiday Date is NOT a weekend day. If a holiday falls on a weekend (like Christmas on a Sunday), you'll double-count its removal. Your original Business Days calculation has already removed the weekend day. Your holiday list should only contain holidays observed on weekdays.

Step 5: Create Your Final "Net Business Days" Calculation

Finally, create one more calculated field that combines our first two methods. Name this one "Net Business Days" and use this simple formula:

[Business Days Elapsed] - ZN([Holidays Between Dates])

We use the ZN() function to treat any NULL values (which occur when there are no holidays) as zero, preventing errors in the subtraction. You now have a robust metric that accurately reflects the number of working days by excluding both weekends and public holidays.

Method 3: Filtering Workloads with a Date Scaffold

The first two methods are perfect for calculating duration. But what if you want to visualize daily activity - like the number of active projects per day - and want your x-axis to completely skip weekends? This is where a date scaffold comes in.

A date scaffold is just a separate data table containing a single calendar with all possible dates over a long period (e.g., every day from 2020-2030).

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 1: Create and Connect Your Date Scaffold

In a spreadsheet, create a single column called [Scaffold Date] and fill it with every consecutive day for your desired range. Then, add this spreadsheet as a new data source in Tableau.

Step 2: Relate the Scaffold to Your Data

Go to the Data Source tab and establish a relationship between your Scaffold and your primary data source using relationship calculations:

• [Scaffold Date] >= [Start Date]
• [Scaffold Date] <= [End Date]

This relationship essentially "expands" each of your records so that it has an entry for every single day it was active.

Step 3: Build the Visualization and Filter Weekends

Now, you can build your visualization. Instead of using your original date fields, use the [Scaffold Date] on your Columns shelf. To exclude weekends, create a filter. Drag your [Scaffold Date] to the Filters card, choose "Weekday," and then uncheck Saturday and Sunday.

The result is a chart that only shows data for business days. The trend line no longer drops to zero on weekends, providing a much clearer picture of your workload during actual workdays.

Quick Tips & Common Pitfalls

• Check for DATETIME values: If your date fields include timestamps (e.g., '10/26/2023 09:15:00 AM'), they are DATETIME types. These can cause small inaccuracies in joins or calculations. If you only care about the date, wrap your fields in the DATE() function, like DATE([Your Datetime Field]).
• Verify Start of Week Settings: Tableau's default week starts on Sunday. You can change this by right-clicking your data source, selecting "Date Properties," and setting the "Week start" to Monday. This can simplify some date logic but remember it affects all DATEPART('weekday', ...) evaluations.
• Test Your Formulas: Before rolling out a new calculation across your dashboards, always test it with some simple, known date ranges.

Final Thoughts

Understanding how to exclude weekends and holidays is a foundational skill for anyone performing business analysis in Tableau. By moving beyond simple DATEDIFF calculations, you align your data with the real-world pace of your operations, ensuring your dashboards on ticket resolution, project delivery, and shipping times are accurate and meaningful.

All these calculations, data joins, and custom formulas require manual setup and a solid understanding of how Tableau handles dates. Over here at https://www.graphed.com/register, we felt this pain and built a solution to automate it. We designed our AI analyst to already understand concepts like "business days," so you can simply ask, "What was our average project completion time in business days last quarter?" and get an instant, accurate answer without ever writing a formula or creating a date scaffold.