Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Subtracting Holidays from Business Days

MadeInHB
8 - Asteroid

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.

 

 

21 REPLIES 21
Philip
12 - Quasar

OK, fixed now.

MarcusDoesBI
7 - Meteor

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
5 - Atom

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

rahulshetty925
8 - Asteroid

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
12 - Quasar

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

rahulshetty925
8 - Asteroid

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
12 - Quasar

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.

Looking at previous days.png

 

 

rahulshetty925
8 - Asteroid

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
12 - Quasar

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

 

DateInput.png

 

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

DateOutput.png

 

rahulshetty925
8 - Asteroid

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

Labels