convert date 1900

[Rainer Welzel]

I tried to force a date column to a text column by
inserting a tick before the date.
This works fine but if the date 01.01.1900
the result is '31.12.1899 .

the macro line is :
Dim cell As Range
...
cell = "'" & cell

what is wrong ?

thx for your help
rainer

 

[Dave Peterson]

maybe you could use:

cell.value = "'" & cell.text

 

[Myrna Larson]

What is wrong is that Excel worksheet dates incorrectly (but intentionally)
treat 1900 as a leap year. It wasn't. On a worksheet, a cell containing the
number 1 is Jan 1, 1900; the number 60 is Feb 29, 1900.

But the error re 1900 not being a leap year is not present in VBA, and the
method used to make the correction was to simply make 60 represent Feb 28,
1900, 1 represent Dec 31, 1899, etc.

So your code will produce "errors" for all dates between Jan 1, 1900 and Feb
28, 1900. You'll could build in a check for that, or write your code this way:

Cell.Value = "'" & Cell.Text