Tag Archives: Formula Field

Difference Between Two Datetime Fields in Salesforce Formula Field

Sample Code:

IF (FLOOR((EndDateTime__c - StartDateTime__c)) > 0, TEXT(FLOOR((EndDateTime__c - StartDateTime__c)) ) & " Days ", "") 
& IF(FLOOR(MOD((EndDateTime__c - StartDateTime__c)* 24, 24 )) > 0, TEXT(FLOOR(MOD((EndDateTime__c - StartDateTime__c)* 24, 24 ))) & " Hours ","") 
& TEXT(ROUND(MOD((EndDateTime__c - StartDateTime__c)* 24 * 60, 60 ), 0)) & " Minutes "
& TEXT(ROUND(MOD((EndDateTime__c - StartDateTime__c)* 24 * 60*60, 60 ), 0)) & " Seconds" 

Salesforce Sample Image Link Formulas

Flags: This formula displays a green, yellow, or red flag image to indicate case priority.

IMAGE( 
CASE( Priority, 
"Low", "/img/samples/flag_green.gif",
"Medium", "/img/samples/flag_yellow.gif",
"High", "/img/samples/flag_red.gif", 
"/s.gif"), 
"Priority Flag")

Colors: This formula displays a 10 x 10 pixel image of a red, yellow, or green, depending on the value of a Case Age custom number field.

IF( Case_Age__c > 20, 
IMAGE("/img/samples/color_red.gif", "red", 10, 10),
IF( Case_Age__c > 10,
IMAGE("/img/samples/color_yellow.gif", "yellow", 10, 10),
IMAGE("/img/samples/color_green.gif", "green", 10, 10)
))

Lights: This formula displays a green, yellow, or red traffic light images to indicate status, using a custom picklist field called Status.

IMAGE( 
CASE(Status__c, 
"Green", "/img/samples/light_green.gif",
"Yellow", "/img/samples/light_yellow.gif",
"Red", "/img/samples/light_red.gif", 
"/s.gif"), 
"status color")

Stars: This formula displays a set of one to five stars to indicate a rating or score.

IMAGE( 
CASE(Rating__c, 
"1", "/img/samples/stars_100.gif",
"2", "/img/samples/stars_200.gif",
"3", "/img/samples/stars_300.gif", 
"4", "/img/samples/stars_400.gif", 
"5", "/img/samples/stars_500.gif", 
"/img/samples/stars_000.gif"), 
"rating")

Circles: This formula displays a colored circle to indicate a rating on a scale of one to five, where solid red is one, half red is two, black outline is three, half black is four, and solid black is five.

IMAGE( 
CASE(Rating__c, 
"1", "/img/samples/rating1.gif",
"2", "/img/samples/rating2.gif",
"3", "/img/samples/rating3.gif", 
"4", "/img/samples/rating4.gif", 
"5", "/img/samples/rating5.gif", 
"/s.gif"), 
"rating")

Priority: This formula displays a red colored priority sign to indicate a priority picklist value.

IMAGE( 
CASE(Priority__c, 
"Very Low", "/img/samples/rating1.gif",
"Low", "/img/samples/rating2.gif",
"Medium", "/img/samples/rating3.gif", 
"High", "/img/samples/rating4.gif", 
"Very High", "/img/samples/rating5.gif", 
"/s.gif"), 
"rating")

Access the User’s Time Zone in a Formula Field

The solution takes advantage of a confusing inconsistency between two out of the box Salesforce functions, namely DATEVALUE and DATETIMEVALUE.

The first evaluates under the user’s timezone, while the latter evaluates as GMT. We can take advantage of this inconsistency and derive the user’s timezone as follows. Paste below code into a new numeric formula field named “UserTimezoneOffset”:

(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 00:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 01:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 02:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 03:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 04:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 05:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 06:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 07:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 08:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 09:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 10:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 11:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 12:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 13:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 14:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 15:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 16:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 17:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 18:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 19:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 20:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 21:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 22:00:00")) - TODAY()) + 
(DATEVALUE(DATETIMEVALUE(TEXT(TODAY())+" 23:00:00")) - TODAY())

This formula works because for every hour offset from GMT, the answer for “what day is today?” differs by one. In other words, the DATEVALUE of any literal hour of the day interpreted as GMT will be a day off from the user’s TODAY() based on their timezone setting on their User record. Adding together each of these discrepancies hour by hour ends up yielding the same answer as their timezone offset.

Formula Field to Find Time Zone From State Field in Salesforce

IF(CASE(BillingState, 'CA', 1, 'NV', 1,'OR', 1, 'WA', 1, 0) >=1, "Pacific Standard Time", null)+ 
IF(CASE(BillingState, 'AZ', 1, 'CO', 1,'ID', 1, 'MT', 1, 'NM', 1, 'UT', 1, 'WY', 1, 0) >= 1, "Mountain Standard Time", null)+ 
IF(CASE(BillingState, 'AL', 1, 'AR', 1, 'IL', 1, 'IA', 1,'KS', 1, 'LA', 1,'MN', 1,'MS', 1,'MO', 1,'NE', 1,'ND', 1, 'OK', 1,'SD', 1,'WI', 1, 0) >= 1, "Central Standard Time", null)+ 
IF(CASE(BillingState, 'CT', 1, 'DE', 1, 'GA', 1, 'ME', 1, 'MD', 1, 'MA', 1,'MI', 1, 'NH', 1, 'NJ', 1, 'NY', 1, 'NC', 1, 'OH', 1, 'PA', 1, 'RI', 1, 'SC', 1, 'VT', 1, 'VA', 1, 'WV', 1, 0) >= 1,"Eastern Standard Time", null)+ 
IF(CASE(BillingState, 'AK', 1, 0) >=1, "Alaskan Standard Time", null)+ 
IF(CASE(BillingState, 'HI', 1, 0) >=1, "Hawaiian Standard Time", null)+ 
IF(BillingState = 'FL', IF(MID(Phone,2,3) = "850","Central Standard Time","Eastern Standard Time"),null)+ 
IF(BillingState = 'IN', IF(MID(Phone,2,3) = "219","Central Standard Time","Eastern Standard Time"),null)+ 
IF(BillingState = 'KY', IF(MID(Phone,2,3) = "270","Central Standard Time","Eastern Standard Time"),null)+ 
IF(BillingState = 'TX', IF(MID(Phone,2,3) = "915","Mountain Standard Time","Central Standard Time"),null)+ 
IF(BillingState = 'TN', IF(CASE(MID(Phone,2,3),"865",1,"423",1,0)>=1,"Eastern Standard Time", "Central Standard Time"),null)