Difference in dates and populating exception

[shankar12]

HI All,

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

category	Start date	End date	Diff in dates	Out put
A	06/02/2018	06/20/2018	18	A exceeded
B	06/02/2018	06/20/2018	18	B exceeded
B	06/05/2018	06/20/2018	15	B exceeded
A	06/05/2018	06/20/2018	15	A exceeded
A	06/04/2018	06/27/2018	23	A exceeded
A	06/04/2018	06/27/2018	23	A exceeded
A	06/05/2018	06/27/2018	22	A exceeded
A	06/05/2018	06/27/2018	22	A exceeded
B	06/06/2018	06/27/2018	21	B exceeded
B	06/06/2018	06/27/2018	21	B exceeded
A	06/07/2018	06/27/2018	20	A exceeded
B	06/07/2018	06/27/2018	20	B exceeded
A	06/12/2018	06/27/2018	15	A exceeded
A	06/12/2018	06/27/2018	15	A exceeded
A	06/23/2018	07/09/2018	16	A exceeded
B	06/23/2018	07/09/2018	16	B 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

Thanks.

[ponraj]

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"))

WorkflowResults

[shankar12]

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.

category	Start date	End date	Diff in dates	Out put
A	06/23/2018	Null		A exceeded
B	06/23/2018	Null		B exceeded

[ramatp30 · Accepted solution]

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

[ponraj · Accepted solution]

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"))))