Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    formatting and Vlookup (xp & 2003)

    I am not sure if this went through so I am resending.

    I am having trouble with formatting of cells so I could use for Vlookup. I am attaching a sample of a work sheet with two (2) tabs which has the information. Tab W1 and Tab S1.


    The ID # in tab W1 & S1 are in different formats. Could the ID # in tab S1 be reformatted to the same format as the ID # in tab W1. The objective is to use the ID# as the “Vlookup” “Lookup_Value”.

    Additionally, in tab W1 the EE_BRTH_DT is recorded as a : 4 digit year, 2 digit month and 2 digit day. Could this be changed to a format such as “01/01/2008”.

    Thanks
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formatting and Vlookup (xp & 2003)

    You can't format the birth date as a date, because Windows doesn't recognize yyyymmdd format.
    You can add two columns with formulas on W1:
    - In cell F2, enter the formula <code>=SUBSTITUTE(D2,"-","")</code>
    - In cell G2, enter the formula <code>=DATE(LEFT(E2,4),MID(E2,5,2),RIGHT(E2,2))</code>
    - Select F2:G2 and fill down as far as needed.
    Column F can be used for lookup purposes.
    You can use Format | Cells to specify a date format for column G.

    See attached version.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •