Below is the content of the date data:
=VLOOKUP(L5, 'M:\Administrative Analyst\VERIZON\VRZN WRLSS\USAGE REPORTS\
[Eligibilty Upgrade Device Report.xls]deviceRpt-1'!$A$1:$J$300, 5, FALSE)

When I use the =CODE(MID($Q$5,ROWS($1:1),1)) formula in a blank cell and get
the #VALUE:
cellQ16=VLOOKUP(L16, 'M:\Administrative Analyst\VERIZON\VRZN WRLSS\USAGE
REPORTS\[Eligibilty Upgrade Device Report.xls]deviceRpt-1'!$A$1:$J$300, 5,
FALSE)
cellQ17=VLOOKUP(L17, 'M:\Administrative Analyst\VERIZON\VRZN WRLSS\USAGE
REPORTS\[Eligibilty Upgrade Device Report.xls]deviceRpt-1'!$A$1:$J$300, 5,
FALSE)
cellQ18==VLOOKUP(L18, 'M:\Administrative Analyst\VERIZON\VRZN WRLSS\USAGE
REPORTS\[Eligibilty Upgrade Device Report.xls]deviceRpt-1'!$A$1:$J$300, 5,
FALSE)

This continues on with the same formulat all the way down to the end of the
column. Any assistance on how to get the data changed into a format that
Excel will recognize as a date data would be greatly appreciated. Because as
of current, it will only accept the data if I manually overwrite the date.

Ron Rosenfeld wrote:
Thanks for the tip...It doesn't seem the cell comes tru for a real date. But
when you look at the cell, it looks like a real date but Excel doesn't
recognize it. I even tried to cut & paste special "Value" and Format to Date
in hopes for Excel to recognize it as a real date. Unfortunately, the
IsNumber formula still results in FALSE. How can I change an entire column
as a date data for Excel to recognize instead of manually typing the date?

That is a little odd because Excel will often recognize date entries even if
they are stored in a cell as text.

So there may either be some incongruity in your system, or possibly some
trailing or leading spaces or non-printing characters.

In order to sort this out, do the following.

Select a cell that contains a date and is giving you an error.
Copy and paste the contents of the formula bar in your response:

Then go to some blank area on your worksheet, (or another workbook or sheet)

Enter the following formula in some cell:

=CODE(MID($A$1,ROWS($1:1),1))

(for $A$1, substitute the cell reference for your error giving date cell; but
BE SURE to use the ABSOLUTE reference style. In other words, if the cell is
Q17, use $Q$17)

Then fill down (copy/drag) until you start to get #VALUE! errors and post those
results here also.
--ron

--
Message posted via http://www.officekb.com