Subtracting Holidays from Business Days

[MadeInHB]

So I have the business days calculating correctly.  Is there anyway to have alteryx look at a list of dates, see if these dates are in the range (Start Date and End Date) and return a 1 if it is and a 0 if it is not?

Example:

Start Date is 2/27/2017

End Date is 3/2/2017

Holiday is 3/1/2017.

[Philip]

OK, fixed now.

[MarcusDoesBI]

Philip you're a star!  I just spent the past several hours trying to solve an issue where we wanted to count the number of days between 2 dates excluding bank holidays (in Germany) and weekends.  Could not for the life of me figure out how to tell Alteryx to subtract a count if holidays fall between 2 dates!

Thank you thank you thank you!

[ncampos]

I'm unable to download the file, are you able to change the file extension.

[rahulshetty925]

Hey Philip,

I have an issue around the same lines, my range of date doesn't include holidays.

Here is a sample.

first file date                       holiday excel sheet

2018-08-31                        2018-09-03

2018-09-04                         2018-11-22

My current formula works if its a friday, gives me monday because of 3.

But because Monday is a holiday , I have to make the calculation 4 manually.

Is there a dynamic way to do this.Thanks.

if DateTimeFormat(DateTimeToday(),"%a") = 'Fri' then 3
elseif DateTimeFormat(DateTimeToday(),"%a") = 'Sat' then 2
else 1 endif

Then create a filter:
[Date] = ToDate(DateTimeAdd(DateTimeToday(), daystoadd, "days")).

[Philip]

@rahulshetty925 can you give me an example input data set and a desired output? I'm not sure I understand the problem.

[rahulshetty925]

Hi Philip,

My input data has only one date field.

Example

Date1 - It has all the business days excluding holidays and weekend

08-30-2018

08-31-2018

09-04-2018

09-05-2018

09-06-2018

I'm using this formula to exclude the weekend and dynamically run the workflow everyday.if DateTimeFormat(DateTimeToday(),"%a") = 'Fri' then 3
elseif DateTimeFormat(DateTimeToday(),"%a") = 'Sat' then 2
else 1 endif

Then create a filter:
[Date] = ToDate(DateTimeAdd(DateTimeToday(), daystoadd, "days")).

The problem here is the counter if DateTimeFormat(DateTimeToday(),"%a") = 'Fri' then 3 failed today as Monday is a holiday.And the workflow showed blank dates.

If I do 4 manually the workflow runs successfully.

But I was thinking if there is a dynamic way in which I can bring in a holiday excel sheet, combine it with my original sheet.

Some more recap to the previous issue.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Exclude-Saturday-and-Sunday-at-Runtime...

Thanks,

Rahul

[Philip]

If I understand correctly, all you need to do is join a list of holidays and take the non-matches from the input. If it doesn't do what you need, let me know and I'll keep working at it.

[rahulshetty925]

Hi Philip,

The workflow that you have attached will work fine if the input data would have those dates, but my input data just skips weekends and business holidays.

Let me give you a brief idea of whats happening.My workflow runs fine today as the logic works for today and tomorrow as the below formula holds true for today.

if DateTimeFormat(DateTimeToday(),"%a") = 'Fri' then 3
elseif DateTimeFormat(DateTimeToday(),"%a") = 'Sat' then 2
else 1 endif

Then create a filter:
[Date] = ToDate(DateTimeAdd(DateTimeToday(), daystoadd, "days")).

I'm trying to put some logic after this formula and not sure if it is possible.Or I will have to populate the input data with business holidays to match with my holiday excel sheet.These two approaches come in mind.Let me know if you can think of any other simple method.Thank You.

Regards,

Rahul

[Philip]

Does your input date data look like this excluding any additional information?

And do you want to expand it to include missing dates, like this?

[rahulshetty925]

Yes Philip,

That looks like the only solution for the time being.Changing the input to have all dates and then exclude weekends and holidays from it.

Right?

Thank You.

Regards,

Rahul