Time Zones function based on Area Codes

Hello,

I'm trying to create a function or macro that will automatically insert the timezone of an area code, and can't seem to get it correct. Any help would be greatly appreciated.

Here's the details. In Column A, I have a list of 10 digit phone numbers. In Column B, I have the first three digits (the area code) of those phone numbers (using the function "=LEFT(A1,3)". In Column C, I want to have excel automatically put in the time zone (eastern, central, mountain, pacific) of the area code in column b.

I have a list of the area codes contained in the time zones, and attempted to use if / then statements to state if the area code is in list E, then put EASTERN in the cell, but I can't seem to get it right. Please help! Thanks!!

-bigjess007


Here's the Timezone/Area code list for reference:
EASTERN
203, 475, 860, 959, 302, 202, 239, 305, 321, 352, 386, 407, 561, 689, 727, 754, 772, 786, 813, 836, 850, 861, 863, 904, 941, 954, 229, 404, 470, 478, 678, 706, 770, 912, 219, 260, 317, 574, 765, 812, 270, 327, 502, 606, 859, 207, 383, 240, 249, 280, 301, 410, 443, 969, 339, 351, 413, 508, 617, 774, 781, 857, 978, 231, 248, 269, 278, 313, 517, 546, 586, 616, 734, 810, 906, 947, 989, 603, 201, 551, 609, 732, 848, 856, 862, 908, 973, 212, 315, 347, 516, 518, 585, 607, 631, 646, 716, 718, 845, 914, 917, 252, 336, 704, 828, 910, 919, 980, 984, 216, 234, 283, 330, 380, 419, 440, 513, 567, 614, 740, 937, 215, 267, 358, 412, 445, 484, 570, 610, 717, 724, 814, 835, 878, 401, 803, 843, 864, 423, 615, 731, 865, 901, 931, 802, 236, 276, 381, 434, 540, 571, 703, 757, 804, 304

CENTRAL
205, 251, 256, 334, 479, 501, 870, 217, 224, 282, 309, 312, 331, 464, 618, 630, 708, 773, 815, 847, 872, 319, 515, 563, 641, 712, 316, 620, 785, 913, 225, 318, 337, 504, 985, 218, 320, 507, 612, 651, 763, 952, 228, 601, 662, 314, 417, 557, 573, 636, 660, 816, 975, 999, 308, 402, 701, 405, 580, 918, 605, 210, 214, 254, 325, 361, 409, 430, 432, 469, 512, 682, 713, 737, 806, 817, 830, 903, 915, 936, 940, 956, 972, 979, 262, 353, 414, 420, 608, 715, 920

MOUNTAIN
480, 520, 602, 623, 928, 303, 719, 720, 970, 208, 406, 505, 957, 385, 435, 801, 307

PACIFIC
907, 209, 213, 310, 323, 369, 408, 415, 424, 442, 510, 530, 559, 562, 619, 626, 627, 628, 650, 657, 661, 669, 707, 714, 747, 752, 760, 805, 818, 831, 858, 871, 909, 916, 925, 935, 949, 951, 808, 702, 775, 503, 541, 971, 206, 253, 360, 425, 509, 564

Try the following...

On Sheet2, set up your list of timezones and corresponding area codes in Columns A through D, as follows...

Please Login or Register  to view this content.

Then, on your main sheet, enter the following formula in C1, and copy down:

=INDEX({"Eastern","Central","Mountain","Pacific"},MATCH(TRUE,COUNTIF(OFFSET(Sheet2!$A:$A,0,{0,1,2,3}),B1)>0,0))

OR

=INDEX(Sheet2!$F$2:$F$5,MATCH(TRUE,COUNTIF(OFFSET(Sheet2!$A:$A,0,{0,1,2,3}),B1)>0,0))

...where Sheet2!$F$2:$F$5 contains a list of your timezones. Both these formulas need to be confirmed with CONTROL+SHIFT+ENTER.

By the way, if you don't already have a table set up as I've described for Sheet2, you can easily set one up by using Copy > Paste Special > Transpose. It only took me a minute or two to set it up. If you'd like I can email you a sample with the table already set up. Let me know if you're interested.

Hope this helps!

Hello,

I am trying to sort area codes into time zones please help!

I have 10 digit #'s (11 counting the 1+2135551212) in column A. I need to sort using the area codes that are already on sheet2 and sorted into their respective time zones. Can I do this with VLOOOKUP?

Thanks