Small but Mighty

just-say-never-doubt-for-all-condition-margaret-mead-quotes-margaret-mead-quotes-never-doubt-936x936

OK, so maybe a small group of citizens doesn’t apply here. But what about a small group of fields? Oh perhaps even just one simple field.

This is a field that I’ve added to many orgs that I’ve worked with, and shared with even more admins who have added it to theirs as well. It’s a field that several Salesforce MVPs have their own twist on. A field that when I tell folks about it, I get some pretty amazing reactions, especially for companies that have high-touch processes. And I can’t believe that it took me 4 years of working with Salesforce before I thought to make this field – and once you hear it, I bet that you will think that same thing.

So what’s the field? A time-zone formula field for Leads, Contacts and Accounts (and really, any other object you what to throw it on). Now you want the formula, right?


IF(ISBLANK(Phone),"--", 

if( CONTAINS("206:209:213:253:310:323:360:408:415:425:503:509:510:530:559:562:604:619:626:650:661:702:707:714:760:775:778:805:818:831:858:867:909:916:925:949:951:971", left(SUBSTITUTE( Phone , "(", ""),3)),"Pacific", 

if( 
CONTAINS("208:250:303:307:385:403:406:435:480:505:520:602:623:719:720:780:801:928:970", left(SUBSTITUTE( Phone , "(", ""),3)),"Mountain", 

if( 
CONTAINS("204:205:210:214:217:218:224:225:228:251:254:256:262:270:281:306:308:309:312:314:316:318:319:320:325:334:337:361:402:405:409:414:417:430:432:469:479:501:504:507:512:515:563:573:580:601:605:608:612:615:618:620:630:636:641:651:660:662:682:708:712:713:715:731:763:769:773:785:806:815:816:817:830:832:847:901:903:913:915:918:920:931:936:940:952:956:972:979:985:", left(SUBSTITUTE( Phone , "(", ""),3)),"Central", 

if( 
CONTAINS("201:202:203:207:212:215:216:219:226:229:231:234:239:240:248:252:267:269:276:289:301:302:304:305:313:315:321:330:336:339:345:347:351:352:386:404:407:410:412:416:418:419:434:438:440:443:450:470:475:478:484:502:508:513:514:516:517:518:519:551:561:567:570:571:540:585:586:603:606:607:609:610:613:614:616:617:631:646:647:649:678:703:704:705:706:716:717:718:724:727:732:734:740:754:757:770:772:774:781:786:802:803:804:810:813:814:819:828:835:843:845:848:856:857:859:860:862:863:864:865:876:878:904:905:908:910:912:914:917:919:937:941:947:954:959:973:978:980", left(SUBSTITUTE( Phone , "(", ""),3)),"Eastern", 

if( 
CONTAINS("800:877",LEFT(SUBSTITUTE(Phone,"(",""),3)),"Toll Free","Other" 

))))))

Now, the science isn’t perfect. As a remote employee, I have a VOIP office number that’s two time zones off. And up until a few years ago, my mobile number was for my home town. So this is not a fool-proof method, but it’s a pretty great start.

On top of that, Amber Neill Boaz shared a great tip on how to refine this functionality even more. I’ll let her say it in her own words:

Amber - TimeZone

Brilliant! Just another simple change to make this functionality even better. One simple field + the power of community = success!

3 Comments to “Small but Mighty”

  1. Thanks for sharing that, definitely a utility that every admin should have in their bag of tricks. I painstakingly built a zip code parser for US codes but when we went international, I installed GoLocal from CronSights and highly recommend that AppExchange app when your global business blooms.

  2. I love this idea! I just went to use it and I am getting an error in the first line saying Extra “,”.

    This is what is causing the problem:

    ISBLANK(Phone),”–“,

    I copied the text word for word. Any idea what I did wrong?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: