Date not acting like a Date as it displays #N/A when using a VLookup

[robertguy]

Hi,

I have been sent a spreadsheet of data which has been downloaded from a main frame, however, I am having a problem with the ‘Date’ field (Column C) as when I run a VLookup to check what week the date falls in some of the dates return #N/A in Column D.

For some strange reason if I select one of the cells with #N/A in and press F2 it seems to rectify itself and displays the week number as required in the corresponding cell in column D.

I’ve checked the cells and they all appear to be Date-Custom-dd/mm/yy

This is driving me insane - can anyone please explain what might be happening and how I can resolve – possible with a macro ?

As ever any assistance in this matter would be greatly appreciated

Rob

N.B. Excel Version 2003

[martindwilson]

some are text
to resolve select column
then data /text to columns
next
next
select dates option
select dmy from dropdown
click finish