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

Multi Field Formula to Count Days between Dates Across Multiple Columns

ColinMarc
7 - Meteor

AlteryxNumberDays.jpg

A through E is what I currently have. I am trying to calculate the number of days between each one of the columns and get the output that is in columns E through J. So for column D2 the calculation to get I2 is the difference between the date and today's date. To get F5 it would be the difference between A5 and E5. Since there are two dates in that row it would be the difference between those two dates. 

 

Basically I am looking to calculate the number of days between each stage but since some stages don't have dates I'm having trouble figuring out what the formula would be to calculate it.

 

Appreciate any help!!

9 REPLIES 9
atcodedog05
22 - Nova
22 - Nova

Hi @ColinMarc 

 

You can try something like below.

 

 

DateTimeDiff(DateTimeToday(),DateTimeParse([Stage 1],"%m/%d/%Y"),"days")

 

 

Output:

atcodedog05_0-1604509692928.png

Workflow:

atcodedog05_1-1604509734805.png

Similarly for other days

 

Hope this helps 🙂


If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

ColinMarc
7 - Meteor

This is very close to what I am looking for but for the one in Stage 1 (Row 5) with the date 10/7/2020 I want the # of days to be the # of days between stage 1 and stage 5. So instead of the difference between the present day and date in Stage 1 it would be the # of days between stage 5 and stage 1.

 

Hopefully this makes sense thank you!

atcodedog05
22 - Nova
22 - Nova

Hi @ColinMarc 

 

Here is the workflow with updated calculation.

ColinMarc
7 - Meteor

Once again this is on the right track, but the solution won't work for every scenario. For example if the date in Stage 1 is 10/2/2020 and there is a date in Stage 2 of 10/10/2020 and then a date in Stage 5 of 10/19/2020. I would want it to calculate the #of days between stage 1 and 2 and then also # of days between stage 2 and 5. If this isn't clear let me know!

atcodedog05
22 - Nova
22 - Nova

Hi @ColinMarc 

 

To get days difference you can use below formula

 

DateTimeDiff(
DateTimeParse([Stage 5],"%m/%d/%Y"), // replace this
DateTimeParse([Stage 1],"%m/%d/%Y") // replace this
,"days")

 

 you can replace [Stage 5] and [Stage 1] the above formula is 

 

A-B <=> [Stage 5]-[Stage 1]

 

Accordingly you can build your calculations.

 

If you can give a expected output we i can build accordingly.

Your logic is still bit fuzzy. Expected output might help.

ColinMarc
7 - Meteor

Here is some more of the expected output, hopefully this makes it clearer now. But I need the formula to be somewhat dynamic. So if there is a date in Stage 1 and 3 it will calculate the # of days between. But if there isn't a date in stage 3 but there is a date for stage 4 it would calculate the # of days between stage 1 and 4.

atcodedog05
22 - Nova
22 - Nova

Hi @ColinMarc 

 

Here is a updated workflow for the task. It should as per your requirement.

 

Check with your new data.

 

Hope this helps 🙂


If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

ColinMarc
7 - Meteor

This is awesome thank you so much!

atcodedog05
22 - Nova
22 - Nova

Happy to help 🙂 @ColinMarc 

 

Cheers and Happy Analyzing 😀

Labels