When Is The Best Time To Call A Lead?

KeyNode Solutions, a leading Salesforce Consulting firm, is always striving to provide the best service possible for our clients, regardless of their requests. To learn more about a recent obstacle we were able to overcome, please continue reading this article. If you have any questions on how we can help your organization grow, contact us at hello@keynodesolutions.com or call us at (858) 215-5371.

We had an interesting challenge recently from one of our clients. They wanted to ensure their sales reps are calling Leads and Contacts at a convenient time during business hours. When contacting a potential new customer, the last thing you want to do is earn a bad reputation with them by calling them while they are having their breakfast or at dinner time. Knowing their local time is important to getting that call off to a good start.

Salesforce doesn’t display the local time for a Lead or Contact so we had to create new fields and automation to calculate the time zone and display the local time on Lead and Contact records.

When we need to see a Lead’s or Contact’s current local time, we will use a formula field which will take the current GMT time and apply a time zone offset to calculate the Lead’s local time. We will display that in a prominent place on the record in Salesforce so our sales team will know exactly what time it is at the location.

Step-by-step how this works (non-technical)

There are two key pieces of information that we have about our leads that can help us determine the local time for a lead: their zip code and the area code part of the phone number.

First, we will try to use the zip code to determine the city, state and time zone of the lead. We use a freely available database of zip codes linked to time zones as our lookup source. There are many sources like this one https://sourceforge.net/projects/zip2timezone/files/ that could be used. We store Zip Code database as a custom object in Salesforce.

If we get a match to the zip code in the lead record, we update the lead city and state (if they were blank) and store the lead’s time zone in the record.

If we don’t get a match to the zip code, we take the phone number, strip out the area code, and look up the time zone using a formula (of course, you will see it below!).

Finally, we have another formula on the lead object to display the local time on the lead page layout for the Salesforce user. This takes the current time at GMT, applies the previously found lead time zone —adjusted for daylight saving if needed—and displays the local time in a user-friendly text format with AM or PM.

The Technical Stuff

Here is the technical detail. This is presented here for those who are comfortable using Process Builder, complex formulas and Flow Builder. If you are not technically minded but have a need for this or a similar solution, give the experts at KeyNode Solutions a call and we will help you.

Create a Process Builder that will run when a lead record is created or updated:

The Process Builder calls a flow whenever a lead is created or updated:

The first task in the flow is to use the following formula to clean-up the zip code we have found on the lead record, making sure there are no leading zeroes.

TEXT(VALUE({!LeadINPUT.PostalCode}))

Next, check that the zip code is not blank and is a number between 3 and 5 digits long with this formula:

AND(NOT(ISBLANK({!CleanZip})), ISNUMBER({!LeadINPUT.PostalCode}), LEN({!CleanZip})>=3, LEN({!CleanZip})<=5)

Locate the Zip Code record and store its city, state, and time zone.

Divide the time zone by 24 to get hours for the offset. This is required because the time zone from the database returns an integer but in order to subtract hours from a time in Salesforce you must divide the number of hours by 24—hours are 24^ths of a day, so subtracting one 24^th would actually subtract only one hour.

{!TimeZone}/24

Set the lead field values to update (City, State, Time Zone Offset).

If no zip code, calculate the time zone offset from the area code using this formula:

if( ISBLANK({!LeadINPUT.Phone}),100,
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({!LeadINPUT.Phone} , “(“, “”),3)),8,
if(CONTAINS(“208: 250: 303: 307: 385: 403: 406: 435: 480: 505: 520: 602: 623: 719: 720: 780: 801: 928: 970”, left(SUBSTITUTE( {!LeadINPUT.Phone}, “(“, “”),3)),7,
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({!LeadINPUT.Phone}, “(“, “”),3)),6,
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”, left(SUBSTITUTE( {!LeadINPUT.Phone} , “(“, “”),3)),5,
if( CONTAINS(“808”, left(SUBSTITUTE({!LeadINPUT.Phone} , “(“, “”),3)),10,
if( CONTAINS(“907”, left(SUBSTITUTE({!LeadINPUT.Phone} , “(“, “”),3)),9,
if(CONTAINS(“800:877”,LEFT(SUBSTITUTE({!LeadINPUT.Phone},”(“,””),3)),100,100))))))))

Format the display of the local time, taking into account daylight saving and appending AM or PM. Daylight saving is calculated by seeing if today’s date is between the second Sunday of March and the first Sunday of November.

IF(Time_Zone_Offset__c=100,”–“,
IF(OR((DATE(YEAR(TODAY()), 11, 1) + CASE( MOD( DATE(YEAR(TODAY()), 11, 1) – DATE( 1900, 1, 7 ), 7 ), 0, 0, 1, 6, 2, 5, 3, 4, 4, 3, 5, 2, 1 ))-TODAY()>238, (DATE(YEAR(TODAY()), 11, 1) + CASE( MOD( DATE(YEAR(TODAY()), 11, 1) – DATE( 1900, 1, 7 ), 7 ), 0, 0, 1, 6, 2, 5, 3, 4, 4, 3, 5, 2, 1 ))<TODAY()) ,

IF(
HOUR(TIMEVALUE(NOW()-Time_Zone_Offset__c)) < 12,
TEXT(HOUR(TIMEVALUE(NOW()- Time_Zone_Offset__c )))&”:”&LPAD(TEXT(MINUTE(TIMENOW())),2,”0″)&” AM”,
IF(
HOUR(TIMEVALUE(NOW()-Time_Zone_Offset__c)) = 12,
TEXT(HOUR(TIMEVALUE(NOW()- Time_Zone_Offset__c )))&”:”&LPAD(TEXT(MINUTE(TIMENOW())),2,”0″)&” PM”,
TEXT(HOUR(TIMEVALUE(NOW()- Time_Zone_Offset__c-1/2 )))&”:”&LPAD(TEXT(MINUTE(TIMENOW())),2,”0″)&” PM”) ),
IF(
HOUR(TIMEVALUE(NOW()-Time_Zone_Offset__c+1/24)) < 12,
TEXT(HOUR(TIMEVALUE(NOW()- Time_Zone_Offset__c +1/24)))&”:”&LPAD(TEXT(MINUTE(TIMENOW())),2,”0″)&” AM”,
IF(
HOUR(TIMEVALUE(NOW()-Time_Zone_Offset__c+1/24)) = 12,
TEXT(HOUR(TIMEVALUE(NOW()- Time_Zone_Offset__c+1/24 )))&”:”&LPAD(TEXT(MINUTE(TIMENOW())),2,”0″)&” PM”,
TEXT(HOUR(TIMEVALUE(NOW()- Time_Zone_Offset__c-1/2+1/24)))&”:”&LPAD(TEXT(MINUTE(TIMENOW())),2,”0″)&” PM”))))

The final result:

If you realized that having the time zone and local time displayed on the record in Salesforce is a great feature, but the number of steps and the formulas look too complex for you, you are welcome to reach out to our San Antonio Salesforce consultants. Salesforce-certified consultants at KeyNode Solutions will be happy to implement this for you. Contact us at hello@keynodesolutions.com or call us at (858) 215-5371.