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



Rainer Welzel wrote:
>
> 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

[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

On Fri, 25 Feb 2005 16:44:53 +0100, "Rainer Welzel" <master@fgg-welzel.de>
wrote:

>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
>