networkdays percent complete

[langs005]

I'm very new to Alteryx and am piloting to try to proof out the business value. I'm very eager to gain some quick wins but have been stuck on converting a date variance that is simple in excel to a usable flow in the tool.

Trying to calculate networkdays as compared to the excel formula shown below, I can get to the count of how many work days or even count of weekend days using generate rows, identify day of week, filter out weekends - but I can't seem to calculate it by row based on the dates (due date-created date) in the data to then bring in the Net working Days that exclude weekends back into the workflow to count for the displayed data.

As you can see this creates a material difference in dates over longer periods.

I also need to calculate the number of net working days between the created date and the last day of a month for a period ending. I figure appending an input text for manually changing that date each month might be easy enough - open to suggestions. 

Finally, I would then need to run the same calculation to get net days and then formula to get difference between the two networkday fields to get the percent complete based on time lapsed.

Project ID	Project Name	Created Date	Due Date	Cost Code	numberofdays	Networkdays btw created and due
123456	ABC	9/5/2019	9/20/2019	BAD1	15	12
123457	DEF	9/19/2019	9/17/2019	BAD2	-2	-3
123458	GHI	4/21/2021	4/29/2021	DAB1	8	7
123459	JKL	12/24/2023	1/31/2024	DEB1	38	28
123460	MNO	6/4/2021	12/29/2023	BED3	938	671

[Carolyn]

Looking at DEF - At least for the issue where the DateTimeDiff is giving you -2 and you want -3, I would take the DateTimeDiff and then subtract 1. It's a sort of cheat-y way to get around that (and how I used to do it in Excel back in the day before I learned about their proper Date Diff formulas)

       DateTimeDiff([Due Date], [Created Date], 'days') - 1

Can you use that with what you've already built to arrive at the Networkdays? 

Edit: Never mind, I don't think that's working how I mean it to... I think I mean +1. Let me play with it some more, but I'm wondering if what you've already built and adjusting for the extra day that is being subtracted out but you want it to count would be enough. 

[Carolyn · Accepted solution]

Reply take 2: There's a macro built and shared Community to solve for this. What you would do is download the macro, save it onto your machine, and then insert it into your workflow. Would that work? 

https://community.alteryx.com/t5/Community-Gallery/Business-Days-count/ta-p/962755

[langs005]

This is exactly what I needed!

Thank you!

[Carolyn]

@langs005 - Great! Can you please mark this as solved? It'll allow others to jump to the solution if they have the same question :)