Hi,
I am trying to get an output of all the cases who were opened for more than 60 days, but is still currently opened and not closed.
IIF(DateTimeDiff([Cased Closed],[Cased Opened],'day') >=60, 'Yes','No')
This formula works to give me 'Yes' for cases that were opened for 60 days, and 'No' if opened for less than 60 days. However, I want the include the AND cases that are still currently opened.
So the 'Yes' would only show if the cases have been opened for 60 days and is still open.
example:
Any help is appreciated!
Solved! Go to Solution.
You can write as:
IF DateTimeDiff([Cased Closed],[Cased Opened],'day') >=60 AND [Status] = 'In Progress'
THEN 'Yes'
ELSE 'No'
ENDIF
This works, however in my particular case -- some of the cases have a Null value for 'Case Closed' as they're still open.
That formula didn't work for this.
IF DateTimeDiff([Cased Closed],[Cased Opened],'day') >=60 AND [Status] != 'Closed'
THEN 'Yes'
ELSE 'No'
ENDIF
example:
I'll provide you with a way to use your IIF() logic:
IIF(DateTimeDiff([Cased Closed],[Cased Opened],'day') >=60 OR [Status] != "Closed", 'Yes','No')
When cased closed is empty or when cased open is in the future, you should handle these conditions better. Just a thought.
Cheers,
Mark
You need a new condition:
IF IsNull([Cased Closed])
THEN 'Yes'
ELSEIF DateTimeDiff([Cased Closed],[Cased Opened],'day') >=60 AND [Status] != 'Closed'
THEN 'Yes'
ELSE 'No'
ENDIF
This worked perfectly and it made a lot of sense.
Thank you @gabrielvilella !