Excel Dates copied to Access Table Changed by 4 Years

[TheThack]

I am copying a series of columns from Excel Spreadsheets into an Access Data Table that is formatted as short date. The cell in Excel that contains the date is formatted as a simple date (*3/15/2005). When I copy the column and paste it into the Access database, the year changes to four years earlier. For example 6/3/2015 becomes 6/3/2011. I am using the MS Office Profession Plus 2010 verions (14.0.7153.5000 - 32 bit).



Why are the dates changing and how can I fix it? The Excel dates are correct.

[June7]

No idea. Never encountered. If you want to provide the Excel for analysis, follow instructions at bottom of my post.

[TheThack]

Apparently it has to do with two different calendar options. Some MS Excel Workbooks (generally on a Mac) use a 1904 calendar while some use a 1900 calendar. In the Options of the MS Excel Workbook under Advanced there is a header called, "When calculating this workbook:" and one of the options you can check is "Use 1904 date system". If that is checked in the workbook, the value of the dates are converted to a number that starts with 01/01/1904 as compared to a number that starts counting days on 01/01/1900. In the 1904 workbook if you enter 1,462 into an empty cell and copy that cell, then select all of the cells with a date and select 'Paste Special' and select add, it will convert the dates to a 1900 number. To go the other way from a 1900 to a 1904 you would select subtract on the 'Paste Special' option. It's extra work but I did not see an easier way. I am still not sure why it is 1,462 and not 1,461 days but apparently the leap year issues is strange (365+365+365+365+1 = 1,461).

[June7]

Learned something new.

So your issue is resolved?

[TheThack]

Important to note: If the date was entered in a workbook setup with the 1904 sytem and you later change it to be the 1900 system, it changes the displayed date to 4 years earlier as it is based on the count of days using the calendar option in place when the date was entered. This means you have to use the copy and paste special option above to change the count of days especially when copying to an Access data base table.

[TheThack]

It is resolved unless somoene knows an easier way to convert without having to copy and paste special.

[June7]

You could maybe run an UPDATE sql on the imported data to add 4 years to the date value of the new records.