topic Re: Subtracting Holidays from Business Days in Alteryx Designer Desktop Discussions

Subtracting Holidays from Business Days

MadeInHB — Thu, 02 Mar 2017 19:38:43 GMT

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.

Re: Subtracting Holidays from Business Days

alex — Thu, 02 Mar 2017 19:52:59 GMT

Can you supply a file as an example. Not sure exactly what you need from your description.

Re: Subtracting Holidays from Business Days

MadeInHB — Thu, 02 Mar 2017 20:02:34 GMT

So right now I have business days calculating. For the example 2/27 - 3/2, it is calculating 3 days. But let's say 3/1 is Holiday. I am looking for a way to have Alteryx see 3/1 as a Holiday and return a 1. Then I can set the formula to do 3-1, to return that it is actually 2 business days and not 3. But I am stuck with the variable holiday dates.

Re: Subtracting Holidays from Business Days

alex — Thu, 02 Mar 2017 20:15:00 GMT

will there be a file with a list of holiday dates ? If so then you can use generate row to create all the dates between the start and end dates, join to the holiday list, filter out weekends and holidays, and then summarize to count the dates.

Search community for "Calculate Business Days Between Dates" for a helpful article.

Re: Subtracting Holidays from Business Days

Philip — Thu, 02 Mar 2017 20:28:25 GMT

There may be a more elegant way, but here is one using Find/Replace Tool. I'd prefer this over Join to avoid duplicating records. Instead, it looks to see if the date is contained in the Holiday list. Note that the orange section is just creating dates to use in the example.

Re: Subtracting Holidays from Business Days

alex — Thu, 02 Mar 2017 20:45:14 GMT

Join wouldn't create duplicate records, but Append Fields would.

Re: Subtracting Holidays from Business Days

Philip — Thu, 02 Mar 2017 22:47:43 GMT

You're right. I was thinking about another situation as I was writing that. Sorry for any confusion!

Re: Subtracting Holidays from Business Days

MadeInHB — Fri, 03 Mar 2017 17:15:09 GMT

Phillip - this was much simpler than the route I was going down. Mine was all over. Thanks very much!!!

Re: Subtracting Holidays from Business Days

Pallavimore001 — Thu, 09 Mar 2017 12:23:01 GMT

Hi, how can I return count of holidays from a given date range? I have two columns with Start Date and End Date and multiple rows with different date ranges - I want to know how many holidays fall within each date range - and have it as an additional column?

Re: Subtracting Holidays from Business Days

Philip — Fri, 10 Mar 2017 16:50:17 GMT

See if this is what you need. I built it off of what MS's documentation says about NETWORKDAYS.

(Hold on, found a couple of errors.)

Re: Subtracting Holidays from Business Days

Philip — Fri, 10 Mar 2017 16:58:28 GMT

OK, fixed now.

Re: Subtracting Holidays from Business Days

MarcusDoesBI — Thu, 02 Nov 2017 20:53:28 GMT

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!

Re: Subtracting Holidays from Business Days

ncampos — Wed, 08 Nov 2017 21:26:34 GMT

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

Re: Subtracting Holidays from Business Days

rahulshetty925 — Fri, 31 Aug 2018 20:46:54 GMT

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")).

Re: Subtracting Holidays from Business Days

Philip — Fri, 31 Aug 2018 21:46:59 GMT

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

Re: Subtracting Holidays from Business Days

rahulshetty925 — Fri, 31 Aug 2018 21:57:29 GMT

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-every-week-from-the/m-p/198612#M50603

Thanks,

Rahul

Re: Subtracting Holidays from Business Days

Philip — Fri, 31 Aug 2018 22:17:20 GMT

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.

Re: Subtracting Holidays from Business Days

rahulshetty925 — Tue, 04 Sep 2018 19:39:05 GMT

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.

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

Re: Subtracting Holidays from Business Days

Philip — Tue, 04 Sep 2018 20:02:51 GMT

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?

Re: Subtracting Holidays from Business Days

rahulshetty925 — Tue, 04 Sep 2018 21:02:53 GMT

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