8 Replies Latest reply on Jul 21, 2015 9:11 AM by Fernando Hanada

# How to calculate business days between two dates without holidays and weekends?

Dears Colegues,

How to calculate business days between two dates without holidays and weekends?

For example I've an order with order date and delivery date, I want calculate how many business days I left to deliver this order without weekends and holidays, because I don't work in this days to deliver orders.

Someone have an ideia?

I found an article in the knowledge base but its the same colunm, and when use 2 colunms didn't use holidays.

Best regards,

Fernando

• ###### 1. Re: How to calculate business days between two dates without holidays and weekends?

How do you define holidays? Globally? For each country? For each city? Based on a business unit?

This topic has been covered in detail a couple of times before so the best starting place is usually those existing threads. I.e. Work Days

• ###### 2. Re: How to calculate business days between two dates without holidays and weekends?

I've 3 kinds of holidays, city, state and country.

I need calculate a lead time of my operation. The lead time of receiving

and delivery order.

I have a record with order date and delivery date in the same table. And in

the other table I have the holidays.

2015-07-20 18:07 GMT-03:00 Tom W <tableaucommunity@tableausoftware.com>:

Tableau Support Community

calculate business days between two dates without holidays and weekends?

Forums - View the full discussion

• ###### 3. Re: How to calculate business days between two dates without holidays and weekends?

Are your tables coming from a database? If so, join the two tables together using the date fields i.e. HolidayDate >= OrderDate and HolidayDate <= DeliveryDate.

Then you would have a left join between the two and could run a calculation based on that as per the thread I linked to previously.

If you would like more specific advice, please provide a Tableau Packaged Workbook including sample data.

• ###### 4. Re: How to calculate business days between two dates without holidays and weekends?

Hi Tom,

No, I have the order table in a database and the holidays in excel.

2015-07-21 11:23 GMT-03:00 Tom W <tableaucommunity@tableausoftware.com>:

Tableau Support Community

calculate business days between two dates without holidays and weekends?

Forums - View the full discussion

• ###### 5. Re: How to calculate business days between two dates without holidays and weekends?

That makes it a bit trickier.

Take a look at the post I originally linked to, it contains a link to this FAQ for Holiday and Workday calculations - The specified item was not found.

You'll see one of the example threads in there I worked through a potential solution joining a database and Excel - Re: Holiday calculation excluding Holidays and Weekends by Region

The difficult you're going to have when you utilize data blending is that you're looking to join between dates which is not supported.

• ###### 6. Re: How to calculate business days between two dates without holidays and weekends?

Thanks, It's really my dificult.

I think that better build a holiday table and make the other solution.

2015-07-21 12:59 GMT-03:00 Tom W <tableaucommunity@tableausoftware.com>:

Tableau Support Community

calculate business days between two dates without holidays and weekends?

Forums - View the full discussion

• ###### 7. Re: How to calculate business days between two dates without holidays and weekends?

It would be easier if the holiday table was also contained within the database.

Then you could effectively do a left join from your Order Table to the holiday table like.

SELECT

o.OrderId,

o.OrderDate,

o.DeliveryDate,

datediff('d', o.OrderDate, o.DeliveryDate) as OrderDaysTotal,

count(h.HolidayDate) as HolidayDayCount,

datediff('d', o.OrderDate, o.DeliveryDate) - count(h.HolidayDate) as OrderDaysMinusHolidays

FROM

Orders o

LEFT JOIN Holidays h on h.HolidayDate between o.OrderDate and o.DeliveryDate

GROUP BY

o.OrderId,

o.OrderDate,

o.DeliveryDate

1 of 1 people found this helpful
• ###### 8. Re: How to calculate business days between two dates without holidays and weekends?

Thanks, I will try this.

2015-07-21 13:10 GMT-03:00 Tom W <tableaucommunity@tableausoftware.com>:

Tableau Support Community

calculate business days between two dates without holidays and weekends?