Return Result Based On One Of Many Cell Values

  • Gotta question:


    I have to convert a row of state codes to the appropriate time zone, and preferably keeping the state code.


    Example - a person types in "ca" under the state column. What I need is for either a function or a program to convert that to "p" in the next column.


    I've looked at some of the solutions posted here and while many solutions seem close I don't think it's what I'm I'm looking for....


    This is converting a list of customers in our office, we have sheets of over 10,000 accounts. Thanks!

  • Re: Converting state abbrev to time zone


    Hi,


    Write a User Defined Function as follows:-


    Code
    Function StateTime(dataString as text)
    select case datastring
    case "CA"
        StateTime = "P"
    ...
    Case Else
        StateTime = "Error"
    End Select
    End Function


    then in column B put "=StateTime(A1)"


    Or something along those lines.


    John

  • Re: Converting state abbrev to time zone


    Surely, an If formula will do that
    =IF(A1="ca","p","")


    You can easily copy down a column by hovering the cursor over bottom right corner of the cell containing the formula until it changes to a cross (+) & double clicking.

  • Re: Converting state abbrev to time zone


    okay when I try to compile the program I get an error msg -


    compile error -


    user-defined type not defined.


    Here is my program:

  • Re: Converting state abbrev to time zone


    It's not as sexy as a custom formula, but I'd just do a VLOOKUP with a table and time zones.


    State Zone
    AL C
    AK E-4
    AS E
    AZ M
    AR C
    (etc)


    =VLOOKUP(A1,Range_with_above_table,2,0)
    i.e. - Y2:Z51

  • Re: Converting state abbrev to time zone


    The reason for the error is because there is no Text data type, try using String instead.


    By the way, no need for that many Cases.

  • Re: Converting state abbrev to time zone


    Just a note, the function is case sensitive ie tx returns an error and TX returns C.

    [SIZE=2]I should change my name to STUMBED![/SIZE]

  • Re: Converting state abbrev to time zone


    Florida's panhandle is Central while the rest of the state is Eastern. Arizona has a couple of places where daylight savings time IS observed, while most of the state ignores it.


    Other oddities may exist that I haven't listed.


    Don't know if this matters, just thought I'd point it out.


    Regards,

  • Re: Converting state abbrev to time zone


    Much as I hate replying to a 5 year old thread - MN is missing from the Central cases. Still useful, even 5 years on though..

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!