Results 1 to 2 of 2
2008-01-10, 23:48 #1
- Join Date
- Jun 2005
- 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”.
2008-01-10, 23:57 #2
- Join Date
- Mar 2002
- 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.