11 Replies Latest reply on Mar 21, 2018 3:19 AM by ade aiyeola

# Excluding Holidays and weekends  in Tableau

hi,

i am able to exclude weekends. Now i need to exclude public holidays from the same code.

Foe excluding weekdays i used below logic

DATEDIFF('week',[Event Start Date],[End Time]) *5

+

MIN(DATEPART('weekday',[End Time]),6)

-

MIN(DATEPART('weekday',[Event Start Date]),6)

and now i want to exclude holidays from the above calculated field.

• ###### 1. Re: Excluding Holidays and weekends  in Tableau

Did you search first?  Simply searching "exclude holidays weekends" on this very forum (or "tableau exclude holidays weekends" in Google) came up with this goldmine:

1 of 1 people found this helpful
• ###### 2. Re: Excluding Holidays and weekends  in Tableau

i  referred all the documents  FAQ:  Holidays & Workdays     and i tried to exclude holidays but i am facing one problem.

in my data set suppose may 2 is a holiday on may 2 there wont be any data and i am trying to blend data and excluding holidays for that particular day which is not correct.

can you tell how to exclude between 29th april to 4th may it would be 2 days after excluding weekends and holiday (2nd may is a holiday).

• ###### 3. Re: Excluding Holidays and weekends  in Tableau

Hi Dinesh reddy,

• ###### 4. Re: Excluding Holidays and weekends  in Tableau

thanks for your response i am attaching sample workbook.

in sheet 1 created a calculated field which give me how many days are there after excluding weekends and what i need is i want to exclude list of holidays from this calculation.

Suppose may 2 is a holiday then calculation will be (4/29/2016 - 5/5/2016) = 4 after excluding only weekends if i exclude holidays it should be 3.

like that i have list of holidays for the entire year to be excluded.

• ###### 5. Re: Excluding Holidays and weekends  in Tableau

Hi Dinesh,

Please check this thread where I have provided the solution on the same along with the Excel and workbook copy attached to the same.

Re: Last 5 day sales for current month excluding weekends/national holidays

Let me know if this one helps.

Thanks and Regards,

Ashish Chaudhari

• ###### 6. Re: Excluding Holidays and weekends  in Tableau

Hi dinesh ,

There is one new thread on the same workaround Mr.Ashish Chaudhari  has provided answer for it  Please check this:Re: Last 5 day sales for current month excluding weekends/national holidays

• ###### 7. Re: Excluding Holidays and weekends  in Tableau

can you upload a .twb work book  not packaged work book (.twbx)because i don't have latest tableau version.

so that i can  downgrade to my version

• ###### 8. Re: Excluding Holidays and weekends  in Tableau

Hi Dinesh,

The attached workbook is on 9.0 version which is done by Mr.Ashish Chaudhari .Please mark his answer as correct if the solution helps you.

• ###### 9. Re: Excluding Holidays and weekends  in Tableau

hi ,

i am not able to open it in 9.o version can you please attach the .twb file.

• ###### 10. Re: Excluding Holidays and weekends  in Tableau

HI,

which version you are using? If you are not able to open 9.0 version the if it is .twbx or .twb then also you cannot access it.

• ###### 11. Re: Excluding Holidays and weekends  in Tableau

Hello Guys,

I can see that this has caused an on going issue.

I recently found a way around it using the formulas below.

Count days excluding weekends

-Calculate the days between dates excluding weekends-

(DATEDIFF('day',[Day1],[Day2]) -

(7-DATEPART('weekday',[Day1])) -

DATEPART('weekday',[Day2])

) / 7*5

+ MIN(5,(7-(DATEPART('weekday',[Day1]))))

+ MIN(5,(DATEPART('weekday',[Day2])-1))

-1

I then created a parameter for the bank/public holiday holiday .i.e. [may bank holiday] and did..

if 'day 2' >= [may bank holiday]

then 'Calculate the days between dates excluding weekends' -1

elseif 'day2' <= [may bank holiday]

then 'Calculate the days between dates excluding weekends'-0

else 'Calculate the days between dates excluding weekends'

end

you can create a parameter for each bank holiday

'if your day1 does not exceed your bank holiday then you dont need this code'

I then went further to see if my start date is after the bank holiday which will totally disrupt my data and went

if 'day1' > [may bank holiday]

then ''Calculate the days between dates excluding weekends' +1

elseif [day2] > = [may bank holidays]

then ''Calculate the days between dates excluding weekends' -1

elseif [day2] > = [may bank holidays]

then ''Calculate the days between dates excluding weekends' -0

else ''Calculate the days between dates excluding weekends'

end

I hope this helps.