Associating a STATE or TIME with a phone number

[charles770]

At my office when I check my voicemails I use Excel 2000 to create a log
sheet of all voicemails received. Then, I start calling people back.
However, to avoid calling people too early or too late, I want to find a way
to automatically display the STATE and/or TIME of the person I am calling
back.

My log sheet is laid out with a column for names, a column for numbers and
a column for notes.

I could add a column for timezones or states, but I want that field to
automatically populate with the appropriate information based on the
telephone number that I enter in the column of phone numbers.

Is this possible?

 

[Bernie Deitrick]

Charles,

Yes, you can use a formula to do what you describe. Create a table with, say, area codes in one
column, and some sort of entry that indicates the time difference, timezone, whatever, in a second
column.

Then use a formula like

=VLOOKUP(AreaCode,TableAddress,2,False)

where AreaCode can be a formula that pulls the area code out of the phonenumber - if you don't have
parens, then use

LEFT(Phone#,3) If you do have parens, then use

MID(Phone#,2,3)

Like this

=VLOOKUP(MID(Phone#,2,3),TableAddress,2,False)


So, perhaps, the formula will end up like this:

=VLOOKUP(MID($A2,2,3),Sheet2!$A$2:$B$1000,2,False)

if your phone numbers are in column A, and your time zone/offset table is on Sheet 2 in the first
two columns.

HTH,
Bernie
MS Excel MVP

 

[JE McGimpsey]

Just be aware that with retention of phone numbers - especially cell
phone numbers - area codes and exchanges are no longer reliable
indicators of geographic location.

Also note that many area codes span time zones (e.g., Idaho's 208 area
code contains regions in both the Mountain and Pacific time zone, and
Indiana counties may or may not use DST, but they're not aligned by area
code)

 

[charles770]

What about doing that, associating a state with the phone number?

 

[Bernie Deitrick]

Charles,

Just as easy - you would need to make up a table with area codes in one column and states in the
other, then use a VLOOKUP based on that table.

HTH,
Bernie
MS Excel MVP

 

[Hile]

If you need help building your table, these are good:

http://www.greatdata.com/ac-timezone.pdf
http://www.greatdata.com/ac-code.pdf

It's about 2 yrs old, but I don't believe area codes pop up every month .