If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Associating a STATE or TIME with a phone number
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? |
#2
|
|||
|
|||
Associating a STATE or TIME with a phone number
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 "charles770" wrote in message ... 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? |
#3
|
|||
|
|||
Associating a STATE or TIME with a phone number
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) In article , charles770 wrote: 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? |
#4
|
|||
|
|||
Associating a STATE or TIME with a phone number
What about doing that, associating a state with the phone number?
"Bernie Deitrick" wrote: 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 "charles770" wrote in message ... 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? |
#5
|
|||
|
|||
Associating a STATE or TIME with a phone number
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 "charles770" wrote in message ... What about doing that, associating a state with the phone number? "Bernie Deitrick" wrote: 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 "charles770" wrote in message ... 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? |
#6
|
|||
|
|||
Associating a STATE or TIME with a phone number
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 :-). -- Hile "charles770" wrote: What about doing that, associating a state with the phone number? "Bernie Deitrick" wrote: 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 "charles770" wrote in message ... 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? |
Thread Tools | |
Display Modes | |
|
|