Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More
We will be upgrading the Gallery to our 2019.4 release this Saturday, December 7th beginning at 9:00am MT. We expect the outage to take last approx. 2.5 hours.

Difference in dates and populating exception


HI All,


I have a data set which required to show difference in 2 dates and based on conditions applied on the category field 




categoryStart date End dateDiff in datesOut put
A 06/02/2018 06/20/201818A exceeded
B 06/02/2018 06/20/201818B exceeded
B 06/05/2018 06/20/201815B exceeded
A 06/05/2018 06/20/201815A exceeded
A 06/04/2018 06/27/201823A exceeded
A 06/04/2018 06/27/201823A exceeded
A 06/05/2018 06/27/201822A exceeded
A 06/05/2018 06/27/201822A exceeded
B 06/06/2018 06/27/201821B exceeded
B 06/06/2018 06/27/201821B exceeded
A 06/07/2018 06/27/201820A exceeded
B 06/07/2018 06/27/201820B exceeded
A 06/12/2018 06/27/201815A exceeded
A 06/12/2018 06/27/201815A exceeded
A 06/23/2018 07/09/201816A exceeded
B 06/23/2018 07/09/201816B exceeded
A 06/23/2018  A exceeded
B 06/23/2018  B exceeded


if A>14 days says "A exceeded" 

if B>21 days sayes "B Exceeded"

 If End date missing "categeory Exceeded(A or B)"


I tried formula for difference in dates i.e DateTimeDiff([start date],[end date],"days") got the difference in dates but for output when i tried this not getting exactly what i am looking 


IF Contains([category],"A") &&
[Diff in dates] > "14"
THEN "A exceeded" ELSEIF Contains([category],"B") &&
[Diff in dates] < "21"
THEN "B exceeded" ELSE "failed" ENDIF





Here is the sample workflow for your case.  Hope this is helpful. Formula used to get desired result as below. Hope this is helpful. 


IIF(Contains([category], "A") and [Difference]>=14, "A Exceeded" , IIF(Contains([category], "B") and [Difference]<=21, "B Exceeded", "Null"))




Thanks Ponraj,


every thing working fine, only one condition is missing if end date is blank i.e null then the exception should be like this below.can you please provide a solution for this.


categoryStart date End dateDiff in datesOut put
A 06/23/2018 Null A exceeded
B 06/23/2018 Null B exceeded



HI @shankar12


Attached workflow should take care of missing condition.


Just added this to formula:

if [EndDate]=NULL() then [category]+' '+"Exceeded" else [Output] endif


Let us know it works


Attached updated workflow. 


IIF(Contains([category], "A") and [Difference]>=14, "A Exceeded" , IIF(Contains([category], "B") and [Difference]<=21, "B Exceeded", IIF(IsNull([Difference]) and Contains([category], "A"), "A Exceeded", iif(IsNull([Difference]) and Contains([category], "B"),"B Exceeded","Null"))))