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

Remove Weekends to find date difference between Two dates

Chhamanshu
7 - Meteor

Hello Community,

 

Can you please let me know how to find the difference between two dates. Here I've to exclude weekends while finding the difference. Attached is the sample data, here I'm doing Pay_Date - Due_Date.

9 REPLIES 9
JosephSerpis
17 - Castor
17 - Castor

Hi @Chhamanshu if you use this syntax DateTimeDiff([Pay_Date],[Due_Date],"days") in a formula tool gives you the differences in days.

 

 

Datetimediff.PNG

 

 

Chhamanshu
7 - Meteor

The solution which you have shared it won't exclude the weekends Saturday and Sunday 

wdavis
Alteryx
Alteryx

Hi @Chhamanshu 

 

The attached workflow should solve your problem. Using a Generate Rows tool, you create each date inbetween your Pay and Due Date Values. You can then use the formula tool to assign weekday names, allowing you to Filter out Weekend Values.

 

You then use the Summarize Tool to get the counts for each of these fields and join to your original dataset.

 

It requires 2 different streams for when Due Date is past Pay Date and Vice Versa, when using the Generate Rows Tool.

 

Let me know if you have any questions!

Capture.PNG

Kind Regards

Will

Chhamanshu
7 - Meteor

It's working. Thanks a lot.

wdavis
Alteryx
Alteryx

Glad to hear @Chhamanshu! Can you mark this as solved in that case, so that other users with similar problems are able to reference this

CMiller15
5 - Atom

Hello,

This solution only appears to work if the event dates themselves don't fall on the weekend.
Is there a more efficient work around or just need to add on a final formula tool to evaluate that scenario and -1?

wdavis
Alteryx
Alteryx

@CMiller15 What is it you're looking to do?

CMiller15
5 - Atom

Just like the original request on this string, I'm trying to determine the number of days between two dates, excluding all dates that are weekends.

In specific, looking to determine the # of business days between a request being made (ticket created) and being solved (ticket closed).

 

The solution above works unless the end date itself is on a weekend. In my situation, we would not consider the date the ticket was closed (if on a weekend) as an additional business day.

RyanDonovanNG
6 - Meteoroid

You could probably pre-process dates to be shifted.  Like if date falls on Saturday then add 2 days.  If Sunday, then add 1.  My guess at least.

Labels