VLOOKUP - retain date for conditional formatting?

**NewYears1978** · 07-21-2017, 01:07 PM

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

Please Login or Register  to view this content.

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

Any ideas?

**6StringJazzer** · 07-21-2017, 01:19 PM

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** · 07-21-2017, 01:27 PM

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** · 07-21-2017, 03:22 PM

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.