community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Count workday difference between 2 dates

Meteor

Hi All,

 

Recently I am working on report automation related to the maturity date difference calculation. We just count workdays in the calculation.

In the forum, I could find many great workflows how to calculate the workdays. Nevertheless, it seems most of them require the steps to define Sat and Sun before we can filter out the weekdays for further calculation.

With loads of entries, Alteryx requires defining Sat and Sun repeatedly in date difference of each entry and it slowed down the performance.

Any ways we can skip the Sat and Sun definition step or just define one time only and applied to all other entries?

 

Eliot 

Highlighted

Assuming you are only dealing with Saturdays and Sundays you can do this using a formula.

 

If you can use a third party library then I have BUSINESSDAYS function within my Alteryx Abacus function library. This reproduces the Excel  NETWORKDAYS function.

 

 Alternatively you can use :

1 + ((DateTimeDiff(End,Start,"days")*5 - (ToNumber(DateTimeFormat(Start, "%w"))-ToNumber(DateTimeFormat(End, "%w")))*2) / 7) + 
IIF(DateTimeFormat(End, "%w")=="6",-1,0) + IIF(DateTimeFormat(Start, "%w")=="0",-1,0)

Sample attached

 

Meteor

Very useful! Really help speed up the whole process, thanks!!!

Atom

Awesome, how cool is that formula!

 

Does make me wonder though (as a pure newbie to Alteryx), why it's not in the base toolset (or is it now?), given how popular it's use is in Excel.

Meteor

Solution

Meteor

You are right. Aniticipating Alteryx team to build the workday formula function for us.

Labels