Loading

Create a time zone field based on the area code in the phone number field

发布日期: Aug 21, 2024
描述
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
解决方案
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})?$")

Related Content: 
How to build a Formula Field: https://help.salesforce.com/articleView?id=customize_formulas.htm&type=5
知识文章编号

000387569

 
正在加载
Salesforce Help | Article