Copied Date Changes When Pasted Into Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

doyle60@aol.com - 29 Nov 2007 20:04 GMT

I created a field in a query that creates the date 1/1/1900.

MyDate: #1/1/1900#

But when I paste this data into Excel, it turns it into this:

2-Jan-00

and when I click in the field in Excel, I can see in the box above
that it is:

1/2/1900

What is up with that? Why does it do that?

I have Access 97.

How can I get it to paste correctly?

Matt

Reply to this Message

Allen Browne - 30 Nov 2007 13:03 GMT

Wow: haven't seen this one raised for a while!

It is actually a bug in Excel. There is a 1 day difference between Access
and Excel on dates before Feb 28 of 1904 (or was it 1900?)

The rumour is that Lotus 123 was confused about whether this was a leap year
or not. When MS origionally built Excel, they built it so it matched the 123
dates. Access doesn't have the bug, and hence there is a difference.

You can test that by adding 1 to the Feb 28 date in Excel, and seeing the
difference between how it behaves and how Access behaves.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I created a field in a query that creates the date 1/1/1900.
>
[quoted text clipped - 16 lines]
>
> Matt

Reply to this Message

doyle60@aol.com - 30 Nov 2007 14:25 GMT

Thanks. I'm preparing data for another computer which uses 1/1/1900
as some sort of null date in date fields---the system not liking
nullities, I suppose.

Matt

Reply to this Message