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!
The Product Idea boards have gotten an update to better integrate them within our Product team's idea cycle! However this update does have a few unique behaviors, if you have any questions about them check out our FAQ.

Alteryx Designer Desktop Ideas

Share your Designer Desktop product ideas - we're listening!
Submitting an Idea?

Be sure to review our Idea Submission Guidelines for more information!

Submission Guidelines

Build in Networkdays Formula Function in Alteryx

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

17 Comments
RachelW
Alteryx Alumni (Retired)
Status changed to: Comments Requested

@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
6 - Meteoroid

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
Alteryx Alumni (Retired)

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
6 - Meteoroid

Thank you Rachel.

 

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

 

Thank you very much.

 

Eric

SeanAdams
17 - Castor
17 - Castor

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

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

Yes please, this would be very helpful

David_Doyle
7 - Meteor

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
Alteryx Community Team
Alteryx Community Team

@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
5 - Atom

Hello

 

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

 

Nikhil