Odd date issue where date is between 1 January 1900 and 1 March 19

[GPO]

Hi,
Access 2000 and Excel 2000.
I have several dates in a column of type datetime that are 1 January 1900.
When I copy/paste these dates into an excel spreadsheet they became 2 January
1900. The underlying integer value of 1 January 1900 in Access appears to be
2, whereas in Excel it appears to be 1.

This is where it gets interesting. This one day discrepancy seems to
manifest itself for all dates up to but not including 1 March 1900. From 1
March 1900 onwards, the dates seem to have the same underlying integer value
in Excel and Access. It appears that one branch of Microsift believes that
1900 was a leap year, and anther branch believes it was NOT. Am I going mad?
Is this an Excel or an Access problem?

Regards

GPO

 

[Allen Browne]

You're not going mad, and the problem is in Excel.

I understand the history of the problem goes like this. Back in the 80s,
Lotus 123 was the market leader in spreadsheets. It wrongly identified 1900
as a leap year. Microsoft wanted to woo the 123 users into Excel without
breaking their existing expressions, so it built Excel with the same error.

They did not build the error into Access, so all these years later we have
this discrepency between the 2 programs. It doesn't arise often, since most
users are not dealing with dates going back that far, but you are correct
about the problem.

 

[GPO]

Thanks Allen,

Rather a priviledge to be responded to be THE Allen Browne ;-)

I'm working around it you converting the date to an unambiguous text string
in Access and back from text to a date in Excel.

Cheers

Greg