VLOOKUP - retain date for conditional formatting?

[NewYears1978]

I have two sheets, sheet1 pulls data from sheet2.

One column is a date but of course vlookup just inputs it as a number. I use conditional formatting ont his column to highlight based on certain dates...but of course this breaks with VLOOKUP.

I am not sure how to do this if it is even possible.
I have tried

=VLOOKUP(A2,New!A:P,12,FALSE)
=TEXT(VLOOKUP(A2,New!A:P,12,FALSE),"dd mm yyyy")

Also formatting the column as a date doesn't have any effect.

Any ideas?

[6StringJazzer]

Is it possible that the data found by VLOOKUP is actually text? Text would not be affected by formatting or the TEXT function that you showed.

I think it would help if you attach your file.

[NewYears1978]

Possibly. The Sheet2 is exported from our system in pure text format..but I tried changing those to dates on that file..which worked. It is really confusing - I will attach sheet. It is column O I am referring to. (last sale)

(I had to remove most the data from sheet2 to get under the filesize limit FYI)

I think based on what you just said I found a solution.

I will add a new column next to my date column and use =Value() then format that as a date. This should work! I will then just hide the other column.

[6StringJazzer]

I think you have a workable solution. The numbers on sheet New are text. One way to tell this is that the string is left-justified. Numbers and dates are right-justified by default. This is a very common issue with date exported from other applications.

[NewYears1978]

Yeah - thank you. I will try my method and see if I can't work it out. Now I have the next issue which is something else so I will post a new thread.