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 Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Calculate difference between two dates and exclude weekends and Country Holidays

mboroto_89
8 - Asteroid

What is the best way to workout a formula that calculates the difference between two dates but also factors in weekends and the country holidays.

E.g if the Difference between two days is 8 and there is a weekend in between, the no of days would be 6 but if there was a public holiday also in between these two days, it would bring the diff to 5.

 

Refer to attached file with first worksheet that has the two dates i want to calculate the difference. Request Creation Date is 29th July and Approval Date is 6th Aug. I've also created a dummy public holiday 5th Aug.

 

What is the best way to calculate the difference between these two dates and exclude the weekends (Sart and Sun) as well as the dummy public holiday.

6 REPLIES 6
atcodedog05
22 - Nova
22 - Nova

Hi @mboroto_89 

 

Here is how you can do it.

Workflow:

atcodedog05_0-1631525334964.png

 

1. Using record id to set unique row id.

2. Using generate rows tool to generate rows between dates.

3. Using filter tool to filter weekends out.

4. Using join tool and taking left unjoin to filter out holidays from the list.

5. Using summarize to get the day count.

 

Hope this helps : )

 

mboroto_89
8 - Asteroid

Thanks, this is super helpful 

mboroto_89
8 - Asteroid

@atcodedog05  thanks  this is super helpful 

atcodedog05
22 - Nova
22 - Nova

Happy to help : ) @mboroto_89 

Cheers and have a nice day!

emartinez
5 - Atom

Is it possible to make it so the inputs run along with the workflow you created? Thank you!  - Nevermind it loaded thank you!

emartinez
5 - Atom

Nevermind it loaded thank you :)

Labels