identify time zone by looking at area code

rishijain11

Board Regular
Joined
Mar 29, 2006
Messages
163
I have a column full of phone numbers. I would like to be able to denote what time zone each number is in, in a separate column for each corresponding row.
How can I do this? I would imagine the formula would look at the first 3 digits for each number and then be able to identify what time zone it is…

Thanks a lot…
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,937
Do you have a table of what area codes are in what time zones, if not, you would need to create that table somehow because Excel does not know time zones for area codes by itself.
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
I have a column full of phone numbers. I would like to be able to denote what time zone each number is in, in a separate column for each corresponding row.
How can I do this? I would imagine the formula would look at the first 3 digits for each number and then be able to identify what time zone it is…

Thanks a lot…
Do you have a table that shows what the time zones are for the area codes?

If so, then you can do a lookup.

See if this helps:

http://contextures.com/xlFunctions02.html
 

rishijain11

Board Regular
Joined
Mar 29, 2006
Messages
163
well, i have a paper list of all the area codes and the time zones for each, i can type that up in some form...
 

rishijain11

Board Regular
Joined
Mar 29, 2006
Messages
163

ADVERTISEMENT

think you can write out the formula for me and how i should enter the area codes and time zones i do have please?
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623

ADVERTISEMENT

think you can write out the formula for me and how i should enter the area codes and time zones i do have please?
What do your phone numbers look like?

Ideally, you'd want the phone numbers in the lookup table to be in the same format as the phone numbers you already have.

Post a few examples.

i have a paper list of all the area codes and the time zones for each, i can type that up in some form...
If you do a web search you might be able to find a site that has this information already in a list format that you can copy and paste into Excel.
 

rishijain11

Board Regular
Joined
Mar 29, 2006
Messages
163
so let's say i have a column of phone numbers in Column A:

973.555.4986
204-555-2643
206.555.5172


then i have a list of the area codes denoting what time zone in another worksheet:
973 = Est
204 = Cst
206 = Pst



basically what i would like is for whenever i add a phone number in column A, it will automatically tell me what time zone it is in by inputting the data into Column B

can you please write up a formula to show me this?
 

rishijain11

Board Regular
Joined
Mar 29, 2006
Messages
163
actually check that, right now my excel file looks like this:

worksheet 1:

in column A the phone numbers

973.555.4986
204-555-2643
206.555.5172


in worksheet two:
in column A:
973
204
206

in column B

EST
CST
PST
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
so let's say i have a column of phone numbers in Column A:

973.555.4986
204-555-2643
206.555.5172


then i have a list of the area codes denoting what time zone in another worksheet:
973 = Est
204 = Cst
206 = Pst



basically what i would like is for whenever i add a phone number in column A, it will automatically tell me what time zone it is in by inputting the data into Column B

can you please write up a formula to show me this?
Create this 2 column table:

973...Est
204...Cst
206...Pst

Let's assume that table is in the range F2:G4.

A2:A4 = phone numbers:

973.555.4986
204-555-2643
206.555.5172

Enter this formula in B2 and copy down to B4:

=VLOOKUP(--LEFT(A2,3),F$2:G$4,2,0)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,461
Messages
5,528,916
Members
409,847
Latest member
Foster034
Top