identify time zone by looking at area code

[banneduser123]

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…

 

[schielrn]

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]

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

 

[banneduser123]

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...

 

[banneduser123]

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

 

[jberwald]

You can do a VLOOKUP. Check it out in excel help.

 

[T. Valko]

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.

 

[banneduser123]

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?

 

[banneduser123]

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]

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)