Count workday difference between 2 dates

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

Output

Optimization

Accepted answers

jdunkerley79

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

Business Days.yxmd

All comments

jdunkerley79

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

Business Days.yxmd

Eliot_with_cat

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

lisaac

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.

Robert2

Solution

Challenge 41.yxmd

LisaLeach

Hello, I would like to add one more layer to this, how do you remove holidays?

apathetichell

hi @LisaLeach - this is a fairly old thread - you may want to start a new one... but also there are a few weekly challenges on this subject. Perhaps this will help?

https://community.alteryx.com/t5/Weekly-Challenge/Challenge-248-Holidates/td-p/685824

If you do start a new thread it helps to include sample data and details of how the information on holidays comes into the workflow (file, manually set, user entered, etc.).

LisaLeach

Ok, I will do that.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703