Change flag colour for case object

Question

I have a requirement here. I have to change the color of a case flag from yellow to red depending upon certain criteria. the criteria is Whenever an Email-to-case is created and Case owner is in "Unassigned Queue" ( Case owner is mapped with a queue ). If the case is in Unassigned queue , then i should display a yellow flag. if the case remains in Unassigned queue for more than 15 minutes, then i should display a red flag. Now, I have created a custom datetime field (say UnassignedDate__c), so whenever Case Owner is moved to Unassigned Queue, then UnassignedDate__c gets populated. I would make the flag to yellow. But i want to check for the red flag condition here. Here, i have to check for EST time for 15 minutes. I tried something like this

IF( NOT (ISBLANK(UnassignedDate__c)) , IMAGE('myyellowimage'), IF( VALUE(ROUND(MOD( ((NOW()-0.1667)-UnassignedDate__c)*1440,60),0)) >= 15, IMAGE('myRedImage'), ''))

I'm not sure what am missing here. I'm unable to get red image. but i'm getting only yellow image.

Answer 1

The first thing that will be checked is whether or not UnassignedDate__c is blank. If it isn't, then the Yellow image will be shown (always) and the rest of the if-statement will be ignored.

Presuming the other part of your formula is correct, the solution would be to first check for the red flag (which is a 'tighter' constraint) and if that is not applicable, check for the yellow flag.

IF( 
    VALUE(ROUND(MOD( ((NOW()-0.1667)-UnassignedDate__c)*1440,60),0)) >= 15
    , 
    IMAGE('myRedImage')
    , 
    IF( 
        NOT (ISBLANK(UnassignedDate__c))
        , 
        IMAGE('myyellowimage')
        ,
        ''
    )
)

Answer 2

I have ended up with the solution for what i need. This may be helpful for you in one way or the other.

IF( AND( NOT(ISBLANK(RM_Unassigned_Date__c)), (FLOOR(( DateNow__c - RM_Unassigned_Date__c)*24)) + ROUND(((( ((DateNow__c - RM_Unassigned_Date__c)*24)- FLOOR((DateNow__c - RM_Unassigned_Date__c)*24)) *60)/100),2) >= 0.15 ), IMAGE("/resource/RedFlag","Red",20,20), IF( AND( NOT(ISBLANK(RM_Unassigned_Date__c)), (FLOOR(( DateNow__c - RM_Unassigned_Date__c)*24)) + ROUND(((( ((DateNow__c - RM_Unassigned_Date__c)*24)- FLOOR((DateNow__c - RM_Unassigned_Date__c)*24)) *60)/100),2) < 0.15 ), IMAGE("/resource/YellowFlag","Yellow",20,20),null ) )

This will give you the flag change depending upon the minutes and hours between 2 datetime fields. DateNow__c contains the value for the NOW() with respect to ur local time zones