Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Number of Dates Between

Highlighted
Asteroid

I'm looking for a way to calculate dates between.

 

This is what I would like the calculation to show.

1/3/20-1/3/20 = 1 day

1/3/20 - 1/4/20 = 1 day

1/3/20-1/5/20 = 2 days

etc...

 

I'm not sure how to get this to calculate correctly with the 1 day's 

Highlighted
Alteryx Certified Partner

Hi @ashleyinman 

 

Try something like this:

 

IF 
DATETIMEDIFF([END_DATE], [START_DATE], 'days') = 0
THEN 1
ELSE DATETIMEDIFF([END_DATE], [START_DATE], 'days')
ENDIF
Magnetar
Magnetar

Hey @ashleyinman 

 

I would recommend using a Formula tool to create your new field. The expression I would use looks like this:

iif(datetimediff(DateTimeParse([end],"%m/%d/%y"), DateTimeParse([start],"%m/%d/%y"), "days") = 0, 1, datetimediff(DateTimeParse([end],"%m/%d/%y"), DateTimeParse([start],"%m/%d/%y"), "days"))

 

Basically, it's saying if the difference between the start and end date is 0, change it to 1. Otherwise, just take the difference in the days. Note here I'm assuming your input looks just like you showed in your original post with mm/dd/yy. In this case, you need to use the DateTimeParse function to change it to a date format before doing the subtraction with the DateTimeDiff.

 

Hope this helps!

 

Capture.PNG

 

Labels