Bug in DATEVALUE command in Excel 2002??

[MattB-UK]

I'm trying to convert a column of around 1600 dates into their Date Numbers
using the DATEVALUE command. 99% of the dates have converted correctly, but
some keep returning the #VALUE error. The source cell properties of correctly
converted and errors ones are identical. When using the 'Show Calcuation
Steps' option the converted value is shown in the cell i.e.
"DATEVALUE(37966)" but the dialog box states that the next evaluation will
result in an error.

Any thoughts??

 

[Arvi Laanemets]

Hi

The value you are trying to convert, isnt a data string. Probably you have a
real date there instead. Format the cell with would-be tate string as text,
select it, press F2 and then enter - probably it will do.

When you have dates and data srings mixed, maybe you try another way:
Format cells with date(string)s as general or number or date.
Into some free unformatted cell, enter the number 1, anc copy the cell.
Select the range with dat(string)s, and then Paste Special Values.
Format converted cells as dates.