Build in Networkdays Formula Function in Alteryx

[E_Liang]

Hi Alteryx User and Alteryx Dev team,

I saw there are number of posts from the community asking for solution to calculate the NetWorkDays (e.g. similar to the networkdays in excel which to calculate the number of days different between the two days excluding weekend and holidays.)

Although we could build a macro for it, the performance is not ideal, especially when the data set is huge and/or the date range required is far apart from each other because there is currently NO a build-in function in Alteryx.  Alteryx will have to expand the date range by date and check whether each is a weekend or holiday.   It will an excellent idea if a build-in function for Networkdays could be built to minimize this hassle from everyone around the world. 

We are looking forward this idea could be take forward.

Thanks

Eric

[RachelW]

@E_Liang Thanks for submitting this ideas post. I'm looking at make some additions to the functions available in Formula. I'll add this request to my list to consider. Can you tell me a little more about how you'd anticipate using a NetWorkDays function and what problems it would help you solve?

Thanks! 

[E_Liang]

Hi Rachel,

Thank you for replying to my message. One of the business case we used Networkday is to calculate the net work days (excluding weekend and country holiday) between deal date and maturity date for some financial instrument.  And the deal date and maturity date for such financial instruments are usually far away from each other (sometime could be more than 30 years).

With existing functions in Alteryx, we have to “expand” the date range and using the existing datetime function check each date individually to determine whether it is a Sat or Sun or Holiday (by inputting another country holiday).  It works ok if there is only small number of records, but performance become a concern when data volume gets huge (say 100,000 or 1,000,000 records).  

I checked in community that a lot of others are sharing the similar concern.  As this is a standard function in excel,  I believe this is a good function worth to make it standard which everyone could be benefited.

feel free to let me know if you any question.

Thank you once again for your advise and response.

Eric

[RachelW]

Thank you for the additional response! This helps me understand the problem you would be trying to solve with the addition of this function. I'll consider it when we add functions to Formula. 

[E_Liang]

Thank you Rachel.

Please do keep me posted and let me know if you need more detail requirement.

Thank you very much.

Eric

[SeanAdams]

Very common need and I think @jdunkerley79 has a set of similar fomulae in his super-hero super-formula pack.

Well worth checking out his blog.

[ksklar]

When I run into this, I use a datediff +1 (since I want an inclusive count) between the start date and end date to get the total number of days, then divide that by 7 to get a weeks integer followed by partial weeks decimal. Separate your decimal portion, divide it by 1/7 and you've got the remainder number of days in excess of full weeks. Now you just need to figure out how many of those remainder days are week days, and you can take your full weeks * 5 week days plus the remainder days to get to net work days. You can get sneaky with the DateTimeFormat function and the %w argument (which returns the day of the week as an integer from 0 to 6 with zero as a Sunday) and a CASE statement to solve the net weekdays in your remainder portion.

(Sorry I don't have an example handy, away from my work computer at the moment). It's not elegant, but it's sure more computationally efficient than expanding the date range.

[AndrewK]

Yes please, this would be very helpful

[David_Doyle]

Has this been added to a later version release yet? I'm currently using a macro to accomplish this but would be great if it was incorporated into the formula tool. Thanks for the plethora of great things Alteryx has already implemented!

[KylieF]

@David_Doyle

This feature hasn't been added to any currently released version of Alteryx. However, this idea is on the product team's radar. Once they have confirmed if this is an feature that can be added to the road map for a future release, this idea will be updated to an Accepted status.

[NBM]

Hello

Just checking if Network Days is on radar of upcoming releases?

Nikhil