cancel
Showing results for
Did you mean:

# Alteryx designer Discussions

SOLVED

## Networkdays in Alteryx

Atom

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

Alteryx

Check out these examples:

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

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

Hope this helps!!

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

Cheers,

NJ

Atom

thanks

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.

Atom

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

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!

Atom

Hello,

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

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