Converting text into a date using Vlookup

[jnathan]

I am attempting to extract a date from a cell that is in string format using a particular VLOOKUP.

This is the VLOOPUP statement (it works!)

Target.Offset(0, 20) = Evaluate("=VLOOKUP(" & Chr(34) & Target.Value & Chr(34) & ",'[Volume Alerts.xlsm] Data'!$A:$W,20,FALSE)")

The date is showing in the 20th column of the Data worksheet in the following format '20140923 08:03:38'

I need the VLOOKUP to be adapted so that it continues to pull in the data in the 20th column but converts it into date format dd/mm/yyyy.

Any help would be much appreciated!

 

[AlphaFrog]

Add this line after the VLookUp to display the date in the preferred format.

Target.Offset(0, 20).CellFormat = "dd/mm/yyyy hh:mm:ss"

 

[mohan.pandey87]

Assuming data in cell A1:

=DATE(YEAR(TEXT(LEFT(A1,FIND(" ",A1)),"0000-00-00")+0), MONTH(TEXT(LEFT(A1,FIND(" ",A1)),"0000-00-00")+0), DAY(TEXT(LEFT(A1,FIND(" ",A1)),"0000-00-00")+0))

 

[jnathan]

Assuming data in cell A1:

=DATE(YEAR(TEXT(LEFT(A1,FIND(" ",A1)),"0000-00-00")+0), MONTH(TEXT(LEFT(A1,FIND(" ",A1)),"0000-00-00")+0), DAY(TEXT(LEFT(A1,FIND(" ",A1)),"0000-00-00")+0))

Is anyone able to show me how the suggested solutions can 'live' within the code?

Target.Offset(0, 20) = Evaluate("=VLOOKUP(" & Chr(34) & Target.Value & Chr(34) & ",'[Volume Alerts.xlsm] Data'!$A:$W,20,FALSE)")

 

[AlphaFrog]

Try this...

result = Evaluate("=VLOOKUP(" & Chr(34) & Target.Value & Chr(34) & ",'[Volume Alerts.xlsm] Data'!$A:$W,20,FALSE)")
result = Replace(result, "'", "")
Target.Offset(0, 20) = [color=darkblue]CDate[/color](Format(Split(result)(0), "0000-00-00")) + [color=darkblue]CDate[/color](Split(result)(1))
Target.Offset(0, 20).NumberFormat = "dd/mm/yyyy hh:mm:ss"

 

[jnathan]

Try this...

result = Evaluate("=VLOOKUP(" & Chr(34) & Target.Value & Chr(34) & ",'[Volume Alerts.xlsm] Data'!$A:$W,20,FALSE)")
result = Replace(result, "'", "")
Target.Offset(0, 20) = [COLOR=darkblue]CDate[/COLOR](Format(Split(result)(0), "0000-00-00")) + [COLOR=darkblue]CDate[/COLOR](Split(result)(1))
Target.Offset(0, 20).NumberFormat = "dd/mm/yyyy hh:mm:ss"

Thanks!!!! Works a treat.

 

[Rick Rothstein]

Try this...

result = Evaluate("=VLOOKUP(" & Chr(34) & Target.Value & Chr(34) & ",'[Volume Alerts.xlsm] Data'!$A:$W,20,FALSE)")
result = Replace(result, "'", "")
[COLOR=#FF0000][B]Target.Offset(0, 20) = CDate(Format(Split(result)(0), "0000-00-00")) + CDate(Split(result)(1))[/B][/COLOR]
Target.Offset(0, 20).NumberFormat = "dd/mm/yyyy hh:mm:ss"

Another way to write the red highlighted line...

Target.Offset(0, 20) = CDate(Format(result, "@@@@-@@-@@@@@@@@@"))

I am not sure where the apostrophe is that you are removing with your second line of code, so I don't know if that line of code is needed or not.