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

Networkdays in Alteryx

pjain2606
5 - Atom

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

13 REPLIES 13
NicoleJohnson
ACE Emeritus
ACE Emeritus

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

pjain2606
5 - Atom

thanks

pjain2606
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.

 

 

pjain2606
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 
Voska
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.

john007
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!   

nahmed29
5 - Atom

Hello,

 

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

32bit
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