How to vlookup a general format cell and convert it to a date

[phillipc1]

Hi all,

I am trying to perform a vlookup to pull in a date that exists as "General" on the source sheet, but I am unsure how to use the formula to reformat it as a "date" on the destination sheet. I have tried using the text function with the vlookup but it was returning #value. I have the information below. I want the date to come in as "m/dd/yyyy" to the destination sheet.

Source Sheet

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	CompanyID	Company	Branch	BranchName	BranchDisplayName	OperationalStatus	ActiveYN	Longitude	Latitude	BranchID	DateOpenedID	DateAcquiredID
2	2	02	0406	REGION 20 SAFETY TRAINING	0406 - REGION 20 SAFETY TRAINING	D	Y	-113.44799	53.578003	405	20160812	20160812
3	2	02	0407	VANCOUVER MFS	0407 - VANCOUVER MFS	D	Y	-122.81996	49.232173	406	20160302	20141101
4	2	02	0409	DOWNTOWN VANCOUVER	0409 - DOWNTOWN VANCOUVER	A	Y	-123.08237	49.279795	408	20160826	20160701
Sheet2

Destination Sheet

Book1
	A	B	C
2	Branch	Dateopened	DateAcquired
3	0406	20160812	20160812
4	0407	20160302	20141101
5	0409	20160826	20160701
Sheet1

Cell Formulas
Range	Formula
B3:B5	B3	=VLOOKUP(A3,Sheet2!C:L,9,FALSE)
C3:C5	C3	=VLOOKUP(A3,Sheet2!C:L,10,FALSE)

 

[Fluff]

How about

=--TEXT(VLOOKUP(A3,Sheet2!C:L,9,FALSE),"0000-00-00")

and format the cell as a date.

 

[dreid1011]

Maybe this if formatting the values as date is not an option, also assuming your current date is yyyymmdd, and not yyyyddmm.

Book1
	A	B	C
1
2	Branch	Dateopened	DateAcquired
3	406	08/12/2016	08/12/2016
4	407	03/02/2016	11/01/2014
5	409	08/26/2016	07/01/2016
Sheet3

Cell Formulas
Range	Formula
B3:B5	B3	=LET(d,VLOOKUP(A3,Sheet2!C:L,9,FALSE),MID(d,5,2)&"/"&RIGHT(d,2)&"/"&LEFT(d,4))
C3:C5	C3	=LET(d,VLOOKUP(A3,Sheet2!C:L,10,FALSE),MID(d,5,2)&"/"&RIGHT(d,2)&"/"&LEFT(d,4))

 

[phillipc1]

Thank you both, these both worked out for me.

 

[Fluff]

Glad we could help & thanks for the feedback.