In this tutorial, we are going to create a formula to calculate the Timezone for an Account and Contact record. Why is this important to use in Salesforce? Here are a few example use cases:

1. Segmenting Reports for more Tactical Sales Calls
2. Workflow rules that can filter based on Customer Business hours
3. Increased Open rates through highly targeted Automated Emails
4. Better Case Management and Customer Experience

Final Timezone Formula:

IF(CASE(BillingState, “CA”, 1, “NV”, 1,”OR”, 1, “WA”, 1, 0) >=1, “Pacific”, null)+
IF(CASE(BillingState, “AZ”, 1, “CO”, 1,”ID”, 1, “MT”, 1, “NM”, 1, “UT”, 1, “WY”, 1, 0) >= 1, “Mountain”, 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”, 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”, null)+
IF(CASE(BillingState, “AK”, 1, 0) >=1, “Alaskan”, null)+
IF(CASE(BillingState, “HI”, 1, 0) >=1, “Hawaiian”, null)+
IF(BillingState = “FL”, IF(MID(Phone,1,3) = “850”, “Central”, “Eastern”),null)+
IF(BillingState = “IN”, IF(MID(Phone,1,3) = “219”, “Central”, “Eastern”),null)+
IF(BillingState = “KY”, IF(MID(Phone,1,3) = “270”, “Central”, “Eastern”),null)+
IF(BillingState = “TX”, IF(MID(Phone,1,3) = “915”, “Mountain”, “Central”),null)+
IF(BillingState = “TN”, IF(CASE(MID(Phone,1,3),”865″,1,”423″,1,0)>=1, “Eastern”, “Central”),null)

Don’t Panic.

Now for a first time formula builder this might look incredibly intimidating. For all formula related posts I will break down the formula in a very specific format: Translations, Functions, and Structure.

1. Translations will be my attempt to spell out parts of the Formula in Plain English.
2. Functions are definitions and explanations of the mathematical functions used
3. Structure is chopping up the formula’s functions into small bits for better interpretation and understanding.

Now If you prefer to skip the tutorial please feel free to copy and paste the formula  into a new (text) formula field on the Account Object. All referenced fields are standard Salesforce fields, but there are a few caveats to be aware of:

1. Your Phone field must follow the standard (123) area code format including parentheses
2. Your Billing State Field must use the two digit Abbreviated Statecode (CA, TX, PA)

If you do not use either of these fields or the fields are formatted differently in your Salesforce instance, then you will have to modify the formula where the fields are referenced. Feel free to contact me or leave a comment if you need any help.

Formula Functions

1. IF() – Determines if expressions are true or false. Returns a given value if true and another value if false.
2. CASE() – Checks a given expression against a series of values. If the expression is equal to a value, returns the corresponding result. If it is not equal to any values, it returns the else result
3. MID() – Returns the specified number of characters from the middle of a text string given the starting position.

Building The Formula Part 1

Here is the first line of the Formula.

IF(CASE(BillingState, “CA”, 1, “NV”, 1,”OR”, 1, “WA”, 1, 0)>=1, “Pacific”, null)+

Translation: “IF the Billing State is (CA, NV, OR, WA) use the value Pacific, if not do nothing”.

Let’s begin by taking a look at the IF statement. Understand that all IF statements follow this simple format.

IF(Boolean Value, TRUE Result, FALSE Result).

A Boolean value is a fancy term for anything that results in a format of True or False, 1 or 0, Yes or No. Here is the formal definition of Boolean if you’re interested. The best example of a Boolean Value in action is a text box field. When the field is checked its set to TRUE and when unchecked equal to FALSE.

What an IF statement actually does, is analyze the Boolean Value and depending on if its True or False, returns whatever is set in the True Result and False Results parts of the statement. This can be anything from a Salesforce Field, Text, a Number, or even other Functions.

Below I have broken down each part of our IF statement in line #1 to make things a little easier to understand.

Boolean Value: CASE(BillingState, “CA”, 1, “NV”, 1,”OR”, 1, “WA”, 1, 0) >=1
True Result: Pacific
False Result: null

Let’s take a look at the CASE function in our Boolean Value.

CASE(BillingState, “CA”, 1, “NV”, 1,”OR”, 1, “WA”, 1, 0) >=1

Here is a simple example to better understand the CASE function.

CASE(Field, Pair Value, Pair Result, No Match Result)

The CASE function is pretty handy when writing Formulas, especially when you use Picklist or Text fields. The CASE function will check the value of a particular field and compares that field’s value against a series of potential values. When there is a match with one of the potential values the paired result is returned.

This is the structure of our CASE function.

Field: BillingState
Pair Values: CA, NV, OR, WA
Pair Results: 1, 1, 1, 1
No Match Result: null

We are using BillingState because the majority of States will only have 1 timezone. The values we are comparing in this particular CASE function are all pacific timezone states (CA, NV, OR, WA). If the BillingState is equal to any of these states the function will return a 1 or the Pair Results. Understand that for each pair value there will always be one pair result, and its important to note that the pair results can be unique values. The final piece of the CASE is this word null for the no match result. Null = Nothing/NONE/NADA. If null is returned your formula will appear blank.

Remember that this CASE function is our Boolean Value, so it needs to be in a True/False format. Notice how there is the “>= 1″ immediately after the case function. We use the >= to create a mini equation to create a Boolean value. If there is a match we will have a simple equation of 1>=1 (True), if there is no match 0 >= 1 (False).

For the more adventurous out there, you could create one very large CASE with the correct timezone pair for each state code. The benefit of using the mini formula method is that you will save characters and its a little cleaner to read/edit. Also its important to note that this method will work for 90% of states, but will not work for states with multiple timezones.

The final piece of line #1 is the “+” at the end. This is the short hand for the concatenate function in Salesforce. Concatenate means combine. Think of our formula as a series of mini formulas, combined together to figure out the exact Timezone given the State/Phone number.

Lines 2-6 have the exact same format as line #1 so I will not go into breaking them down. The only difference are the States listed in the CASE function and the TRUE Result of the IF Statement, which is their respective timezone. I would encourage you to take a look at each one to better understand how the formula works.

Building The Formula Part 2

Let’s jump over to lines 7-11. These lines follow a completely different format, that’s even MORE complicated.

IF(BillingState = “FL”, IF(MID(Phone,1,3) = “850”, “Central”, “Eastern”),null)+
IF(BillingState = “IN”, IF(MID(Phone,1,3) = “219”, “Central”, “Eastern”),null)+
IF(BillingState = “KY”, IF(MID(Phone,1,3) = “270”, “Central”, “Eastern”),null)+
IF(BillingState = “TX”, IF(MID(Phone,1,3) = “915”, “Mountain”, “Central”),null)+
IF(BillingState = “TN”, IF(CASE(MID(Phone,1,3),”865″,1,”423″,1,0)>=1, “Eastern”, “Central”),null)

Now stick with me, because this is where you can gain some other coding experience. These crazy looking lines are dealing with the remaining 10% of states that have multiple timezones. Thankfully no state has more then two timezones, so using an IF statement is a perfect solution.

Take a look at Line 7.

IF(BillingState = “FL”, IF(MID(Phone,1,3) = “850”, “Central”, “Eastern”),null)+

Translation: “IF The Billing State is FL, then check IF the Area code is EQUAL TO 850, IF it is 850 then return Central, IF it is not ‘850’ then return Eastern”.

For this part of the formula we use two IF statements, this is known as a Nested IF statement. We build the formula out this way because only the area code 850 in the state of Florida is a central area code, all others are Eastern.

Here is the structure of our first IF Statement.

Boolean Value: BillingState = “FL”
True Result: IF(MID(Phone,1,3) = “850”, “Central”, “Eastern”)
False Result: null

The Boolean Value checks if the Billing State is Florida. Simple enough right? We put the 2nd IF statement in the True Result because we only want to check the area code IF the State code is Florida. For other formulas you can put the second IF statement in the False Result or even Both, but let’s save that for another day.

Rewritten another way our formula looks like this,

IF(BillingState = “FL”, Nested IF, null)

This is the structure of the second IF statement

Boolean Value: MID(Phone,1,3) = “850”
True Result: “Central”
False Result: “Eastern”

Translation: “IF the AREA Code is 850, Return Central, IF not return Eastern”.

The MID(Phone,1,3) is telling Salesforce to look at the Phone field and check the area code. Make sure your field follows the (850) data structure, or this will not work properly.

The MID function checks within any text and starts at a certain character that you specify and then returns a certain number of characters after the initial one. Think of it this way, “Look at the Phone Field, Start at the 1st character, and then return the next 3 characters”. Since the 1st character is the “(“, we want to return the next 3 characters or “850”.

So if the area code is in fact “850” = “850”, our IF statement will be True and return “Central”, IF Not “Eastern”.

Now the best for last.

Line 11 is combining EVERYTHING you have learned up until this point into one amalgamated frankenstein formula. If you have made it this far, hopefully you will have an idea of what is going on, but let me explain.

IF(BillingState = “TN”, IF(CASE(MID(Phone,1,3),”865″,1,”423″,1,0)>=1, “Eastern”, “Central”),null)

Everything is exactly the same as Line 10 except we have now added a CASE function inside of our 2nd IF statement and left out the “+” at the end. The CASE works in the exact same way as Line 1, but in this instance is checking for two different area codes instead of States. We also omitted the “+” because there is no other lines to combine with after line 11.

The final step is to create a new Formula Field on the Account Object and make sure you select the “Text” formula field type. The best way to check the formula is working properly is to create a Summary Report for accounts and group the data by the Timezone field.

Congratulations! You have successfully built out a formula that automagically calculates the Account or Contacts Timezone.

Photo Credit: LeoPlus