Networkdays in Alteryx

[pjain2606]

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

[KaneG · Accepted solution]

Check out these examples:

• Knowledge Base Article: https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Calculate-Business-Days-Between-Dates/ta-p/3...
• Forum questions:
  • https://community.alteryx.com/t5/Data-Preparation-Blending/Datetimediff-using-only-weekdays-business...
  • https://community.alteryx.com/t5/Data-Preparation-Blending/Subtracting-Holidays-from-Business-Days/m...
  • https://community.alteryx.com/t5/Data-Preparation-Blending/DateTimeAdd-to-Only-Result-in-a-Business-...

That should give you an overview of a few different methods, as well as additional stuff like holidays etc.

[NicoleJohnson · Accepted solution]

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

Hope this helps!!

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

Cheers,

NJ

[pjain2606 · Accepted solution]

thanks

[pjain2606]

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]

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

Date1	Date2	No of Days
5/27/2017	6/30/2017
5/30/2017	6/30/2017
5/28/2017	6/30/2017
5/28/2017	6/30/2017
5/28/2017	6/30/2017
5/28/2017	6/30/2017
5/30/2017	6/30/2017

[Voska]

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]

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]

Hello,

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

[32bit]

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?