function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Sheree KennerSheree Kenner 

formula to return missed if time is greater than 30

Hello,

I have one field that is called "TimetoAssignSLA__c" which consists of the following formula:

IF(BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c > 0 ,TEXT( FLOOR( BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c ) ) & " days "& TEXT( FLOOR( MOD( (BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c ) * 24, 24 ) ) ) & " hours "
& TEXT( ROUND( MOD( (BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c ) * 24 * 60, 60 ), 0) ) & " minutes","")

That works exactly as I need it to and now I need to create a new field called "SLAMet" that determines if the result of "TimetoAssignSLA__c" column is >30 minutes. If so, I want the results to say "MISSED". If <30 minutes, I want the results to say "MET". 
User-added image

Would someone be so kind to help me with the formula for the SLA Met? Please note, I will need the field type as well to ensure I set the fornula up properly. Also, is there a way if it's MISSED to make the text RED and if it's MET to make the text GREEN? If so, would you please include that in the formula as well?

Formulas aren't my strong suit and any help would be most appreciated. 

Thanks,
Sheree
Best Answer chosen by Sheree Kenner
Santosh Kumar 348Santosh Kumar 348
try this it should work :
  
IF(
AND( 
BMCServiceDesk__StatusChangeDate__c  -  BMCServiceDesk__openDateTime__c  > 0, 
FLOOR( BMCServiceDesk__StatusChangeDate__c  - BMCServiceDesk__openDateTime__c  ) < 1, 
FLOOR( MOD( (BMCServiceDesk__StatusChangeDate__c  - BMCServiceDesk__openDateTime__c  ) * 24, 24 ) ) < 1 , 
ROUND( MOD( (BMCServiceDesk__StatusChangeDate__c  - BMCServiceDesk__openDateTime__c  ) * 24 * 60, 60 ), 0) > 30
),
"Missed" ,"Met" )

Good Luck Sorted out now :) 

Regards,
Santosh

All Answers

Santosh Kumar 348Santosh Kumar 348
Hi Sheree,
 Try below formula for your "SLAMet" Field. 
IF(AND(BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c > 0, FLOOR( BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c ) ) < 1, FLOOR( MOD( (BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c ) * 24, 24 ) ) < 1 , ROUND( MOD( (BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c ) * 24 * 60, 60 ), 0) > 30),"Missed" ,"Met" )
For making the text red it will be easier if you create 2 Image for "Missed" and "Met" in respective colours and instead of returning "Missed" or "Met" from formula field you can returnt the image that will work for you.

You need to use image tag for that sample code is below:
  
IF(AND(BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c > 0, FLOOR( BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c ) ) < 1, FLOOR( MOD( (BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c ) * 24, 24 ) ) < 1 , ROUND( MOD( (BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c ) * 24 * 60, 60 ), 0) > 30), IMAGE("/img/samples/light_red.gif",'Missed')  , IMAGE("/img/samples/green.gif",'Met') )

Refer below link if you need any help for how to add image and use it in Formula Field:

https://help.salesforce.com/articleView?id=000327122&type=1&mode=1


Do let me know if it helps you and close your query by marking it as solved.

Regards,
Santosh
Sheree KennerSheree Kenner
Thank you so very much for your reply!!! This is great and I will test this immediately. Upon testing, I get that the highlighted comma (before “Missed”) is “extra” and I’m not familiar enough with the formulas to know why. IF(AND(BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c > 0, FLOOR( BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c ) ) < 1, FLOOR( MOD( (BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c ) * 24, 24 ) ) < 1 , ROUND( MOD( (BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c ) * 24 * 60, 60 ), 0) > 30),"Missed" ,"Met" ) I really appreciate you taking the time to help and for the assistance with both formulas. Best, Sheree
Santosh Kumar 348Santosh Kumar 348
If it works please mark the answer as close as this will help others as well. If you are still facing any issue do let me know.

Regards
Santosh Kumar
Sheree KennerSheree Kenner
Hello Santosh, everything looks great. Both formulas, however, say that the comma before the comma here: 30)," is extra. Any thoughts? I created the images as suggested and refered to the KB on how to add to the formula you provided. Thank you.
Santosh Kumar 348Santosh Kumar 348
Ohh Sorry My bad, the bracket ")" after 30 needs to be removed. This was extra bracket which was closing the If statement there itself.
  
IF(AND(BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c > 0, FLOOR( BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c ) ) < 1, FLOOR( MOD( (BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c ) * 24, 24 ) ) < 1 , ROUND( MOD( (BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c ) * 24 * 60, 60 ), 0) > 30, IMAGE("/img/samples/light_red.gif",'Missed')  , IMAGE("/img/samples/green.gif",'Met') )


The simple one without image: 
IF(AND(BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c > 0, FLOOR( BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c ) ) < 1, FLOOR( MOD( (BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c ) * 24, 24 ) ) < 1 , ROUND( MOD( (BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c ) * 24 * 60, 60 ), 0) > 30,"Missed" ,"Met" )

Do let me know if it helps you and close your query by marking it as solved.

Regards,
Santosh
Sheree KennerSheree Kenner
Hi Santosh, no worries. I am a novice, so I appreciate any all help you can provide. Here is what I am getting now: IF(AND(BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c > 0, FLOOR( BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c ) ) < 1, FLOOR( MOD( (BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c ) * 24, 24 ) ) < 1 , ROUND( MOD( (BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c ) * 24 * 60, 60 ), 0) > 30,"Missed" ,"Met" ) Error: Incorrect parameter type for function 'AND()'. Expected Boolean, received Number Thanks Sheree
Santosh Kumar 348Santosh Kumar 348
try this it should work :
  
IF(
AND( 
BMCServiceDesk__StatusChangeDate__c  -  BMCServiceDesk__openDateTime__c  > 0, 
FLOOR( BMCServiceDesk__StatusChangeDate__c  - BMCServiceDesk__openDateTime__c  ) < 1, 
FLOOR( MOD( (BMCServiceDesk__StatusChangeDate__c  - BMCServiceDesk__openDateTime__c  ) * 24, 24 ) ) < 1 , 
ROUND( MOD( (BMCServiceDesk__StatusChangeDate__c  - BMCServiceDesk__openDateTime__c  ) * 24 * 60, 60 ), 0) > 30
),
"Missed" ,"Met" )

Good Luck Sorted out now :) 

Regards,
Santosh
This was selected as the best answer
Sheree KennerSheree Kenner
Perfect!!! Thank you!
Sheree KennerSheree Kenner
Hey Santosh, I am getting odd results with the formula. Some are saying they’ve been met, but are definitely not met. Some that are hours and days over the 30 minute assign time are saying they are met. Ideas? IF( AND( BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c > 0, FLOOR( BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c ) < 1, FLOOR( MOD( (BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c ) * 24, 24 ) ) < 1 , ROUND( MOD( (BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c ) * 24 * 60, 60 ), 0) > 30 ) [cid:image003.jpg@01D64001.016C0650]
Santosh Kumar 348Santosh Kumar 348
Sorry was trying to utilise the formula given by you, but finally thought to run and check the same by myself now I have changed the formula as per your requirement and tested it as well it's working. I have used lastmodifed and createddate replace it with your original field. 
IF(
AND(  
FLOOR( LastModifiedDate - CreatedDate  ) = 0, 
ROUND(MOD((LastModifiedDate - CreatedDate)*24,24),0) = 0 , 
ROUND(MOD((LastModifiedDate - CreatedDate)*1440,60),0) < 30
),
"Met" ,"Missed" )


Regards,

Santosh
Sheree KennerSheree Kenner
Nailed it!!! Thank you so much. I need this SLA measurement and you saved me. I made a minor adjustment to the fields names, but it works perfectly! IF( AND( FLOOR( BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c ) = 0, ROUND(MOD((BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c)*24,24),0) = 0 , ROUND(MOD((BMCServiceDesk__StatusChangeDate__c - BMCServiceDesk__openDateTime__c)*1440,60),0) < 30 ), "Met" ,"Missed" ) Thanks, Sheree