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.
Solved! Go to Solution.
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"))
Workflow
Results
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 |
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"))))