That would convert it to a number, but it still wouldn't be seen as a date
by Excel and therefore wouldn't format to the proper date.

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
Or even try

=--A1

or

=A1+0

or

=A1*1

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Tom Ogilvy" wrote in message
...
That is because the date isn't being stored as a date I would suspect -
it
is being stored as a number or as text.

You would have to break it into pieces and make it a date

assume the value is in A1

=DATE(--(LEFT(A1,4)),--MID(A1,5,2),--RIGHT(A1,2))

would be the basic formula. You now need to replace A1 with the
location
of
the cell

ActiveCell.FormulaR1C1 =
"=VLOOKUP(RC[-23],LCS_Complete_E90.xls!C1:C12,2,0)"

sStr = "VLOOKUP(RC[-23],LCS_Complete_E90.xls!C1:C12,2,0)"
sStr1 = "=DATE(--(LEFT(A1,4)),--MID(A1,5,2),--RIGHT(A1,2))"
sStr1 = Replace(sStr1,"A1",sStr1)
ActiveCell.FormulaR1C1 = sStr1
ActiveCell.NumberFormat = "dd-mmm-yy"

--
Regards,
Tom Ogivy


"Tempy" wrote in message
...
Hello Bob,

The Part number is my look up value which i use to copy the Purchase
order number over whith, i then want to copy the date over as
mentioned
before, this format is "General" and the cell it is going to is
Formatted
dd-MMM-yy.

I have treid a manual vlookup but the date remains the same ?

Tempy

*** Sent via Developersdex http://www.developersdex.com ***