community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Community v19.6

Looks aren't everything... But the latest Community refresh looks darn good!

Learn More
SOLVED

Add business days to date?

Highlighted
Atom

Hi all,

 

New Alteryx user here, excited to put the program into use with my team.

 

I looked through some posts but nothing quite helped with adding a number of business days to a date. Everything I found had how to calculate business days in between, which doesn't help me from what I can tell.

 

I have a date in a column. I would like  a new column to be [date] + 120 business days. What's the best way to go about this? 

 

Thank you!

Alteryx Partner

I've never had to solve this one before, but check out the attached. Might need some tweeks/tuning but hopefully gets the job done, I think.

 

Bolide

I would just figure out exactly how many days out 120 weekdays is and add that.  120 weekdays = 24 weeks = 168 days.  So just add 168 days.  If you wanted to take it up a notch, create a table/excel spreadsheet with company holidays and include a "next business day" column.  Join the 2 dates together, anything that comes out of the inner join should then use the "next business date" column.

Aurora
Aurora

The easy way to do this is:

- Add a generate rows tool to generate 150 or so rows (you don't need 150 but just to give you space)

- then use a formula to do a date-add based on your generated ID

- Then use a formula to add "day of week"

        - If you are in middle-east, then mark any row with Friday or Saturday as Non-working; and Sunday -Thu as working

       - if you are in the west, do the same but Sat & Sunday

      - This is done using a formula tool with a switch statement

...You now have a load of rows, with a day of week and a "is working day flag."

 

- Filter out the days outside of your date range using filter tool

- Use a summarize to count the number of days that have the "is working day" flag set to true

 

This will work robustly for any date period or starting day.

 

Happy to mock this up if you need?

Atom

This is great, thank you. It works well for a table with one record, but doesn't work for tables with multiple records (provides one output date for all records no matter if input dates are different). Any ideas on how to work around that?

Alteryx Partner

Gotcha. OK try this. I have two records coming in now, so multiple records should work.

Atom

Awesome, thanks for the prompt response, that worked great. I'll have to look into how you did that, I'm still learning how all these tools work. Thanks again!

Alteryx Partner

Your welcome. If you have questions after you poke around, let me know. I'd recommend looking at the differences between what I originally sent vs the follow up, too. The Multirow Formula Tool is *really* handy a lot of the time.

Labels