Cell.Text different from Cell.Value when dates are concerned...

[Travis Vandersypen]

A company I work for is using Excel to export and import data into other
parts of the program. User A will export data into an Excel file and give it
to User B who then imports that Excel file into their database. Since there
are several Memo/Text fields in the data, the entire export is done doing
automation writing each field's value cell by cell.

Recently, we have discovered that in Excel 2007, although the Cell's Text
field say 1/1/1900, when we query the Value property of that cell it reads as
12/31/1899 12:00:00 am. I could understand the date-time portion of this, but
why does Excel subtract an entire day from the date specified in the Text?
(Please note that the export simply sets the Cell's Text property without any
further consideration to formatting to simulate the way in which a user would
type into the Cell)

I'm thinking this is a definite bug in Excel, but do not know how to report
it to Microsoft. I also find it hard to believe that I would be the first
person to discover this issue, so any help would be greatly appreciated....

 

[Mike H]

Hi,

Well it is a bug but an intentional one.

Excel followed on from Lotus 123 and in Lotus there was a genuine bug where
it treated 1900 as a leap year when in fact is wasn't. In order to allow
migration from Lotus to Excel without the need to change dates, Microsoft
continued with the bug and do so to this day. VB displays the correct date
for the value which is 1 day earlier.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Jacob Skaria]

It is a known bug in Excel which prevents dates from 1/1/1900 through
2/28/1900 from being calculated properly. You just need to adjust by 1. Lotus
1-2-3 incorrectly treated 1900 as a leap year and for compatibility reasons
this error was maintained so...

 

[Mike H]

I should have added that once past day 60 the 'bug' disappears and worksheets
and VB evaluate as the same
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.