Calculation of Working Days Excluding Plant-Specific Holidays and Sundays

Scenario:

In multi-plant manufacturing or supply chain environments, accurately measuring working days is essential for planning, forecasting, and performance tracking. A common oversight is failing to account for plant-specific holidays, which can lead to operational misalignment, missed delivery deadlines, and inaccurate reports. Hence, non-working days—Sundays and local holidays—must be excluded from calculations to reflect the true timeline between an Order Date and Dispatch Date.

Requirement:

The objective is to calculate working days between the Order Date and Dispatch Date, while excluding:

• All Sundays (standard weekly off)
• Plant-specific holidays (maintained in the Holiday Master)

This approach ensures an accurate reflection of operational timelines for any given order period.

About the Use Case:

This logic is applied across the following datasets:

•  Units Data: Includes Order Date, Dispatch Date, and Plant Code.

• Holiday Master: Maintains holiday entries specific to each plant.

We have Holiday Master which consists of Holiday Name, Date with their respective Plant Code.

• Weekend Master: Lists Sundays (or any additional weekend rules if extended in future).    

(Note: Only Sundays are considered as weekends)

Below is the expected the output:

This use case ensures that business timelines accurately reflect actual working availability, thereby enhancing SLA tracking, improving forecasting accuracy, and driving greater operational transparency. As a result, it significantly contributes to better process accountability and informed decision-making.

Our Goal is Straightforward

Calculate the number of working days (normal business days) between the Order Date and Dispatch Date, excluding Sundays and plant-specific holidays.

Logical Steps to Calculate Working Days:

Step 1: Gather and Combine Data

• Merge the order data with holiday and weekend data.
• Ensure each order is cross-checked against plant-specific holidays and all Sundays.

Step 2: Calculate Non-Working Days

• For each record, count the number of holidays and Sundays between the Order Date and Dispatch Date.

Step 3: Calculate Actual Working Days

• Subtract the non-working days (Sundays and holidays) from the total number of days between Order Date and Dispatch Date.

Formula:

Working Days = (Dispatch Date - Order Date + 1) - Holiday Count - Sunday Count

Both start and end dates are included.

Steps to create the example source report in Smarten.

Step 1: Create a data source for Holiday Master, Sunday Master and Sales data from the CSV/Excel file and then create the datasets from the created source. 

For this instance, we will use an Excel file as the source.

Datasource for Holiday Master

 Datasource for Sunday Master

 Datasource for Sales Data 

Below is the resultant datasets

Holiday Master Dataset

Sunday Master Dataset

Sales Dataset

Step 2: Now add the Holiday Master and Sunday Master datasets to the main Sales dataset.

Step 3: Now join the Holiday Master with the Sales dataset.

The SQL query provided performs a selection of specific sales data from a sales table and associates it with a holiday calendar table to count the number of holidays that fall within the period between the Order Date and the Invoice Date for each record.

The below is the required SQL Query: 

Now join the Sunday Master with the Sales dataset

The SQL query provided is designed to calculate the number of Sundays that fall within a given sales transaction period, along with other relevant sales information. Here's a detailed breakdown of the query:

The below is the required SQL Query:

Step 4: Now calculate the Working Days 

Working Days:  

dateDiff (“d", Dispatch_Date, Order_Date) - HolidayCount - SundayCount + 1

Conclusion:

This method provides an accurate working day count by removing non-operational days tied to both general weekends and plant-specific holidays. The result is a more truthful reflection of actual business timelines, ensuring better scheduling, resource allocation, and SLA tracking.