Search This Blog

Tuesday, April 19, 2011

Time Zone formula field based on Area Code

A while back I found myself scouring the web for anything that would allow us to look up the time zone of our account records based on their area code. I stumbled across a great Salesforce.com site/blog with this formula. I have not been able to find it again but if anyone recognizes it, let me know and I will give them the credit they deserve. This is a great formula. We have made some additions to it to account for new area codes and provide additional time zones accounting for Hawaii and Alaska. Here's the code. 

Time Zone Formula Field based on Area Code:

if( ISBLANK(Phone),"--",

if( CONTAINS("206:209:213:253:310:323:360:408:415:425:503:509:510:530:541: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:575:602:623:701: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:260: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:574: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:317:315:321:330:336:339:345:347:351:352:386:401:404:407:410:412:413:416:418:419:423:434:438:440:443:450:470:475:478:484:502:508:513:514:516:517:518:519:540:551:561:567:570:571: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:765:770:772:774:781:786:802:803:804:810:812:813:814:819:828:835:843:845:848:850:856:857:859:860:862:863:864:865:876:878:904:905:906:908:910:912:914:917:919:937:941:947:954:959:973:978:980:989", left(SUBSTITUTE( Phone , "(", ""),3)),"Eastern",

if(
CONTAINS("808", left(SUBSTITUTE( Phone , "(", ""),3)),"Hawaii",

if(
CONTAINS("907", left(SUBSTITUTE( Phone , "(", ""),3)),"Alaska",

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

))))))))

1 comment:

  1. Not sure if this is the post that you are referring to but similar time zone formula content can be found here...http://www.akacrm.com/blog/35-sfdc-insight/148-finding-time-zone-for-contacts.html#

    ReplyDelete