Tag Archives: Formula Field

Formula Field to Convert 15 Character ID to 18 Character ID

Follow the below steps to create a formula field that will give you the 18 characters ID of the records.

  • Go to Setup | Customize | object name | click Fields
    (For Custom objects: Setup | Create | Objects | Object Name)
  • In the related list “Custom Fields & Relationships” click New.
  • Click the Formula radio button.
  • Click the Text radio button for “Formula Return Type”.
  • 6. Input the following Formula into the Formula Editor:
    CASESAFEID(Id)
  • Set Field Visibility, add/ remove from Page Layout(s).
  • Click Save.

NOTE: When dealing with record types only custom record types have ids.

How to create a report that shows user license?

To display Salesforce as a User License type on report, Below is the workaround:

  • Create a Custom Formula Text field under the User object:
    1. Go to Setup | Customize | Users | Fields
    2. Click on New and select “Formula” field type and then “Text”
    3. Use “Profile.UserLicense.Name” in the formula editor
    4. Click on Save
  • Create a New Administrative User report:
    1. Go to the Report tab
    2. Click on New report
    3. Under the Administrative reports | Select Users
    4. Add the New Custom Formula field in the report
    5. Run the Report and Save

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)