Results 1 to 7 of 7
  1. #1
    TheThack is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2015
    Posts
    4

    Excel Dates copied to Access Table Changed by 4 Years


    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.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    No idea. Never encountered. If you want to provide the Excel for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    TheThack is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2015
    Posts
    4
    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).

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    Learned something new.

    So your issue is resolved?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    TheThack is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2015
    Posts
    4
    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.

  6. #6
    TheThack is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2015
    Posts
    4
    It is resolved unless somoene knows an easier way to convert without having to copy and paste special.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    You could maybe run an UPDATE sql on the imported data to add 4 years to the date value of the new records.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 13
    Last Post: 01-01-2014, 10:28 PM
  2. Replies: 1
    Last Post: 02-12-2013, 02:33 AM
  3. Repost: Dates shifting between access and linked excel table
    By lucasjkr in forum Import/Export Data
    Replies: 3
    Last Post: 11-14-2012, 03:08 PM
  4. in between dates pulling all years
    By mejia.j88 in forum Queries
    Replies: 2
    Last Post: 04-25-2012, 10:49 AM
  5. Populating 10+ Years of Dates Table
    By Mordred in forum Access
    Replies: 2
    Last Post: 04-27-2011, 06:40 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums