Alteryx Designer

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

Networkdays in Alteryx

Highlighted
5 - Atom

How to apply Networkdays formula in Alteryx, where dyas between the two days is calculated and weekend and holidays are not considered

Highlighted
14 - Magnetar
14 - Magnetar

There isn't a native formula, but you can get there with a few tools (and a table of holiday dates if you want to exclude those as well). See attached.

 

1. Generate rows for all dates between your start & end date (add a RecordID before this step if you have more than one date pairing you're wanting to calculate this for)

2. Formula tool to determine the number designation for each date (DateTimeFormat([Date],"%w")

3. Filter for Weekday numbers not equal to 0 (Sunday) or 6 (Saturday) <-- If you have a non-standard workweek, such as Sunday/Monday weekends, you could adjust these #'s

4. Join a list of holiday dates to the True output of your formula, if you would also like to exclude holidays. This has to be a manual input (or pulled from a table somewhere) as the definition of "holiday" varies so widely! :)

5. Summarize tool: Group by RecordID, start date, end date, etc. and then count the number of records (days) remaining

 

Networkday.JPG

Hope this helps!!

 

EDIT: ... which appears to be basically the same solution as those from the articles @KaneG included. :)

 

Cheers,

NJ

Highlighted
5 - Atom

thanks

Highlighted
5 - Atom

I want to find the no of days between two dates , and if there is a weekend then it should not count weekends between those two dates.

 

 

Highlighted
5 - Atom

example no of days between 1 June'17 till Jul'17 and avoiding weekends in a single row:-

 

Date1Date2No of Days
5/27/20176/30/2017 
5/30/20176/30/2017 
5/28/20176/30/2017 
5/28/20176/30/2017 
5/28/20176/30/2017 
5/28/20176/30/2017 
5/30/20176/30/2017 
Highlighted
8 - Asteroid

Just wanted to point out that using this method, you will lose any records that have a negative count or 0 count between days.  When you join back the row count to your original row count.. look at what isn't joining.. these are all your 0 or negative day counts.  If you need these records, you'll need to use a different method to get a count for them and union them back.

Highlighted
5 - Atom

NicoleJohnson,

 

Thank you so much for this workflow.  I struggled greatly trying to find the right solution and this was the only one that worked.  You are a genius.  Thank you!   

Highlighted
5 - Atom

Hello,

 

Am trying to use Generate row tool but my start and end dates are getting changed. File attached for your reference

Highlighted
8 - Asteroid

Hello,

 

Has this been implemented in Alteryx yet? This seems like a simple ask as all the building blocks are there and a simple loop that shouldn't take more than a few days for a developer to test out. Are there really so few Alteryx users that need to calculate business days that this hasn't been a larger issue over the years?

Labels