Calculate by Adding 2 Days from Date

Hi Guys,

Would someone help me how to get the date by 2 days but will not count weekends (Saturday and Sunday) as well as US holidays?

For example my date is September 1, 2023 (Friday)

Sept 2 and 3 are weekends so it will not be included in the count

Sept 4 is US Labor Day

So if I am going to add to days, the date should be September 6, 2023.

Please help me derive the formula.

Thanks and hope to hear from someone.

Kamen

Workflow

Date Time

Common Use Cases

Accepted answers

rzdodson

@KamenRider I have adjusted the solution from above and am reposting the file for you here.

I removed these tools for you (see below).

I also went ahead and loaded 10 years worth of US holidays in the attached solution for you since you cannot access external links. Let me know if it does not work out for you.

Here is what the workflow looks like (for now).
Solution 1.png

Accounting for Holiday.yxzp

All comments

rzdodson

@KamenRider are there any other countries holidays that need to be excluded? I am assuming Labor Day is the US' Labor Day.

Qiu

@KamenRider
I went to this site and download the list of US holidays for 2023.

Then basically we will generate a fairly large range dattes from specified date then filter out weekends and Holidays.

Hope this will help.

0915-KamenRider.yxzp

rzdodson

@KamenRider here is another approach.

The analytic app solution generates a list of holidays for a user-determined number of years (first section). Then, in the second section, I use the URLs created in the first section to bring in all holidays for that country over those years. Third, and finally, I test the date against whether we are on a weekend and there is a holiday. If those conditions are not met, I add two days to the Test Date.

Hope that helps you out!

Solution.yxzp

KamenRider

Hi @rzdodson

Only US Holiday for now.

Thanks for asking.

Kamen

rzdodson

@KamenRider Not a problem. If you need additional countries, I have preloaded the app solution I posted above with the list of countries that should work for the app. Would require some minor configuration (another Drop-Down tool for country selection).

KamenRider

Hi @rzdodson

Got an error. Can you help me debug? See below error.

Thanks,

Kamen

rzdodson

@KamenRider it could be a number of different reasons. There is pretty detailed Alteryx Community that is posted here:

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Error-transferring-data-Failure-when-receiving-data-from-the/td-p/86980.

Alternatively, I can run the number of years for the US for the next N number of years for you, switch the input to a yxdb for the holidays in that period, and then remove the JSON parse section. Dealer's choice.

KamenRider

Hi @rzdodson

I suspected because of the external links. Our company does not allow external links. Is there any other way you could modify the workflow?

What is JSON parse section? I apologize, I am not much well verse in Alteryx, still learning.

Thanks,

Kamen

KamenRider

Hi @Qiu

Your workflow seems to get the logic of my problem. May I know why did you use "add seven days' in the formula instead of 2 days as per my requirement?

Need to know so I can also explain it to my colleagues.

Thanks,

Kamen

rzdodson

@KamenRider I have adjusted the solution from above and am reposting the file for you here.

I removed these tools for you (see below).

Accounting for Holiday.yxzp

KamenRider

Hi @rzdodson

This looks good. Thanks.

Kamen

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

michael_weaver 335

Hollingsworth 335