Two IF statements in one formula
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Labels:
- Date Time
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
You can write as:
IF DateTimeDiff([Cased Closed],[Cased Opened],'day') >=60 AND [Status] = 'In Progress'
THEN 'Yes'
ELSE 'No'
ENDIF
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
This worked perfectly and it made a lot of sense.
Thank you @gabrielvilella !
