Print this page

How to create a time zone field based on the area code in the phone number field?

Knowledge Article Number 000220169
Description How to populate the time zone automatically on the basis of area code. This article explains it in detail

Use Case - Details from leads are captured in the org and later Sales team need to call the prospective customer as per the customer's time zone
Resolution We can create a formula field by using the below formula and once we enter the phone number, time zone would be displayed automatically in the formula field.

Understanding the formula 
 
if( ISBLANK(Phone),"--", 
This checks if the Phone is Blank, it displays --
 
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:236:250:604:778:867", left(SUBSTITUTE( Phone , "(", ""),3)),
"UTC-8 Pacific", 

This checks if the first three digits of Phone number matches the area code and if it matches the area code UTC-8 Pacific is displayed. If the area code doesn’t match then it will check the second IF. The same is repeated.
 
In the below formula, We are checking the first three digits of the phone numbers and based on these digits we display the time zone. For example : 206 is area code for Seattle which falls under Pacific Time Zone so 
UTC-8 Pacific  will be displayed. 

Please find below the complete formula, this also includes Canadian area codes. 
 
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:236:250:604:778:867", left(SUBSTITUTE( Phone , "(", ""),3)),
"UTC-8 Pacific", 

if( 
CONTAINS("208:250:303:307:385403:406:435:480:505:520:602:623:719:720:780:801:928:970:403:587:780", left(SUBSTITUTE( Phone , "(", ""),3)),"UTC-7 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:204:306:431:639:807", left(SUBSTITUTE( Phone , "(", ""),3)),"UTC-6 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: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: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:226:249:289:343:
365:416:437:438:450:514:519:579:581:613:647:705:819:873:905", left(SUBSTITUTE( Phone , "(", ""),3)),"UTC-5 Eastern", 

if( 
CONTAINS("506:709:902",LEFT(SUBSTITUTE(Phone,"(",""),3)),"UTC-4 Atlantic", 

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

Please note that for forcing the entry of phone as per 999-999-9999 format the following Validation can be used

!REGEX(Cust_Phone__c,"^(\\d{3}\\-\\d{3}-\\d{4})?$")




promote demote