4 Replies Latest reply on Mar 21, 2018 3:32 AM by ade aiyeola

# Calculate days between two dates - Excluding weekends and bank holidays ( No SQL)

Hi all,

I hope someone can help me.

I know the above is a recurrent topic and I am wondering if there is any solution for the above issue. I need to work out the days past between to dates excluding weekends and bank holidays.

I have done a bit of searching on the forum and I have seen that there are some solutions ifyou extract data from a SQL database, however in my case I need to use an excel file.

Any ideas how to do this?

Regards

Roberto

• ###### 1. Re: Calculate days between two dates - Excluding weekends and bank holidays ( No SQL)

Hi Roberto,

Whilst calculating working days is possible (see here) I've not seen a solid solution for excluding bank holidays.

However, if you're using Excel, can you use the NetWorkDays function in Excel...before you bring it into Tableau?

• ###### 2. Re: Calculate days between two dates - Excluding weekends and bank holidays ( No SQL)

Hi Roberto, Are your dates in a single column, or multiple columns?

If in a single column, this may help; it has a flag specifically for bank holidays. US Holiday Date Flags 2010-2020, to share

• ###### 3. Re: Calculate days between two dates - Excluding weekends and bank holidays ( No SQL)

Hi

Neil - Ultimately I can do it on Excel, this is for a monthly dashboard that is why I was hoping to do it through Tableau so I can avoid manipulate it in Excel.

Jennifer - Data is in two different columns unfortunately. Thanks for showing that post, it could be useful in the future!

• ###### 4. Re: Calculate days between two dates - Excluding weekends and bank holidays ( No SQL)

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.