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!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

Calculate Business Days Between Dates

AndrewL
Alteryx
Alteryx
Created

Question

How do I calculate the number of business days between two dates?

Answer

We get this question a lot in Customer Support. It's actually fairly easy to do using a just a few tools.

Assuming your beginning and end date are in two separate fields you can take a look at the sample attached to demonstrate the process.

1) Use the Generate Rows tool to fill in the missing dates

2) Use a Formula tool to create a field with the day of the week for each of the filler dates

3) Use a Filter tool to remove the unwanted days of the week

4) Use the Summarize tool to count the number of days that are left

Note that if you don't already have an unique ID on each record, you'll want to use the Record ID tool to add one to make the summarize process easier.

The sample workflow was built in 10.6, but the same general process can be followed in all versions.

Attachments
Comments
AdamWatson
7 - Meteor

I am having trouble opening up the example file (yxzp) am i missing something ?

 

Solved my problem now please ignore

MahanteshS
8 - Asteroid

Hi Sir,

 

(DateTimeDiff(["2018-01-10","2018-01-23",'Business Days')) >= 15

 

How can i code it in alteryx, I am getting confuse,  

jwarbus
5 - Atom

Is there a way for this calculation to return a negative number? There are times within my date where the end date is prior to the begin date. It is valid and I need it to return the negative number.

Akemi
5 - Atom

any ways to do that in-db? I tried searching ways to copy generate rows function but didn't find clues

KPtaekyunkim
5 - Atom

Thanks for this!

 

I've been scratching my head on this for moment as I've been "formulating" columns as an array.  Then I realized the "Generate Rows" tool can generate the array in a row perspective.  Much easier to maintain while keeping the column structure static.

LisaLeach
6 - Meteoroid
Hello, I was able to add the flow above to my process. However, I need to add an additional layer of removing holidays. How would I go about that? Example. Start 1-8-2021 End 1-19-2021 minus the 1-18-2021 holiday should equal 7 days between Thank you
apurba1989
5 - Atom

Thanks a lot for the solution, it was really helpful.